Mysql update query with IF statement

Question

I'm learning to use mysql so i created a really simple project manager program. i wanted to track if a user saw her/his tasks in a project. So i want to update user_seen_task filed when a project is got queried by a user, but obviously i only want to update this field if it isn't already set.

This is my approach what i have already:

UPDATE task 
SET user_seen_task = 1, user_seen_task_date = NOW() 
WHERE project_id = 14 AND user = 4 AND user_seen_job != 1

Unfortunately this query will update user_seen_task_date every time it runs.

Any help is greatly appreciated.

Stefan Steiger · Accepted Answer · 2014-07-25 10:11:10Z

1

UPDATE task 
SET user_seen_task = 1
    , user_seen_task_date = CASE 
                               WHEN  user_seen_task_date IS NULL THEN NOW() 
                               ELSE user_seen_task_date 
                            END 
WHERE project_id = 14 
AND user = 4 
AND user_seen_job != 1

Or if it doesn't need to update user_seen_task in this case, just add

AND user_seen_task_date IS NULL

into your where clause

UPDATE task 
     SET user_seen_task = 1, user_seen_task_date = NOW() 
WHERE project_id = 14 AND user = 4 AND user_seen_job != 1
AND user_seen_task_date IS NULL

answered Jul 25, 2014 at 10:11

Stefan Steiger

83k70 gold badges405 silver badges461 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql update query with IF statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related