使用 IF 条件的 MySQL UPDATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1886928/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL UPDATE using IF condition
提问by moleculezz
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), .....]
用户 [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)]
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:
我现在拥有的是这个，但这仅在用户存在角色并且其 end_date 为 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
回答by Martin Beeby
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:
由于唯一的区别是您是将 ur.end_Date 设置为当前日期还是将其设置为现有日期，因此您不能只使用以下内容：
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
