使用IF条件的MySQL更新 [英] MySQL UPDATE using IF condition
问题描述
我什至不确定这是否可行,但是如果满足特定条件,我将尝试执行其他UPDATE.
I'm not even sure if this is possible but I'm trying to do different UPDATE if a certain condition is met.
用户[id,开始日期(NOT NULL),结束日期(NULL),原因(NULL),.....]
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)]
user_roles [id, role_id, user_id, start_date(NOT NULL), end_date(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
推荐答案
由于唯一的区别是您是将ur.end_Date设置为当前日期还是将其设置为现有日期? /p>
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
这篇关于使用IF条件的MySQL更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!