mysql - 根据 IN 子句设置为 bool 值

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;