设置为基于 IN 子句的布尔值

Question

如何在单个查询中执行以下操作？

UPDATE clients SET online=0 WHERE id NOT IN(4,5,8,10,12) AND id>=2 AND id<=15 AND parentId=123;
UPDATE clients SET online=1 WHERE id     IN(4,5,8,10,12) AND id>=2 AND id<=15 AND parentId=123;

Answer 1

您可以使用CASE .. WHEN语句：

UPDATE clients 
SET online = CASE WHEN id IN(4,5,8,10,12)
THEN 1 
ELSE 0 
END
WHERE
id BETWEEN 2 AND 15 AND parentId = 123;

---

IN(..)是一个逻辑/比较函数。因此，您也可以执行以下操作(仅在MySQL中(：

UPDATE clients 
SET online = (id IN(4,5,8,10,12)) 
WHERE
id BETWEEN 2 AND 15 AND parentId = 123;