MySQL UPDATE using IF condition

Question

I'm not even sure if this is possible but I'm trying to do different UPDATE if a certain condition is met.

user [id, start_date(NOT NULL), end_date(NULL), reason(NULL), .....]

user_roles [id, role_id, user_id, start_date(NOT NULL), end_date(NULL)]

What I have right now is this but this only works if a role exists for the user and it's end_date is NULL:

UPDATE user p 
JOIN user_roles ur ON p.id = ur.user_id 
SET ur.end_date = NOW()
   , p.end_date = NOW()
   , p.reason = "Retired" 
WHERE p.id = 5 
AND ur.end_date IS NULL

I was thinking of doing something like this:

IF EXISTS (SELECT id FROM user_roles 
WHERE user_id = 5 AND end_date IS NULL)
THEN 
    UPDATE user p 
    JOIN user_roles ur ON p.id = ur.user_id 
    SET ur.end_date = NOW()
        , p.end_date = NOW()
        , p.reason = "Retired" 
    WHERE p.id = 5 AND ur.end_date IS NULL 
ELSE 
    UPDATE user 
    SET end_date = NOW()
        , reason = "Retired" 
    WHERE id = 5 
END IF

@APC, I was doing same thing =)

Rubens Farias
– Rubens Farias

2009-12-11 09:48:51 +00:00
Commented Dec 11, 2009 at 9:48 — Rubens Farias
– Rubens Farias, Commented Dec 11, 2009 at 9:48

shauryachats · Accepted Answer · 2015-03-11 16:58:33Z

14

Since the only difference is whether you are setting ur.end_Date to either the current Date or setting it to it's existing date could you not just use the following:

UPDATE user p  
JOIN user_roles ur 
ON p.id = ur.user_id  
SET 
ur.end_date = IF (ur.end_date IS NULL, NOW(), ur.end_date),
p.end_date = NOW(), 
p.reason = "Retired" 
WHERE p.id = 5

edited Mar 11, 2015 at 16:58

shauryachats

10.5k4 gold badges38 silver badges48 bronze badges

answered Dec 11, 2009 at 14:24

Martin Beeby

4,6091 gold badge30 silver badges21 bronze badges

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

1 Comment

moleculezz Over a year ago

That makes much more sense indeed... it works perfectly. Thanks Martin!!

Collectives™ on Stack Overflow

MySQL UPDATE using IF condition

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related