如何使用不同的WHERE对UPDATE进行单一查询

Question

我想用这个语句进行单个查询，但我不知道是如何实现的

UPDATE alarm SET alarm1 = 1 , alarm1time = NOW() WHERE alarm1 != 1 AND id = 1;
UPDATE alarm SET alarm2 = 1 , alarm2time = NOW() WHERE alarm2 != 1 AND id = 1;
UPDATE alarm SET alarm3 = 1 , alarm3time = NOW() WHERE alarm3 != 1 AND id = 1;
UPDATE alarm SET alarm4 = 1 , alarm4time = NOW() WHERE alarm4 != 1 AND id = 1;
``

Answer 1

您可以使用CASE表达式：

UPDATE alarm
SET
alarm1 = CASE WHEN alarm1 <> 1 THEN 1 ELSE alarm1 END,
alarm1time = CASE WHEN alarm1 <> 1 THEN NOW() ELSE alarm1time END,
alarm2 = CASE WHEN alarm2 <> 1 AND THEN 1 ELSE alarm2 END,
alarm2time = CASE WHEN alarm2 <> 1 THEN NOW() ELSE alarm2time END,
alarm3 = CASE WHEN alarm3 <> 1 THEN 1 ELSE alarm3 END,
alarm3time = CASE WHEN alarm3 <> 1 THEN NOW() ELSE alarm3time END,
alarm4 = CASE WHEN alarm4 <> 1 THEN 1 ELSE alarm4 END,
alarm4time = CASE WHEN alarm4 <> 1 THEN NOW() ELSE alarm4time END
WHERE id = 1;

这只需一条update语句就可以完成任务。话虽如此，我不喜欢我写的东西，实际上我更喜欢你使用几个不同更新语句的版本，因为它干净且更容易阅读。如果您想以原子方式运行4个更新，那么可以从单个显式事务中执行。

Answer 2

WHERE id = 1 AND ( alarm1 != 1 OR alarm2 != 1 OR ... )

应该可以，但每个数据库使用不同的语法，所以( )可能是个问题。