mysql if update_使用IF条件的MySQL更新

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

解决方案

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