如何在单个查询中执行以下操作？
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;
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;
hmtdttj41#
你可以用 CASE .. WHEN 声明：
CASE .. WHEN
UPDATE clients SET online = CASE WHEN id IN(4,5,8,10,12) THEN 1 ELSE 0 ENDWHERE id BETWEEN 2 AND 15 AND parentId = 123;``` `IN(..)` 是逻辑/比较函数。因此，您还可以执行以下操作（仅限mysql）：
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 clientsSET online = (id IN(4,5,8,10,12))WHEREid BETWEEN 2 AND 15 AND parentId = 123;
1条答案
按热度按时间hmtdttj41#
你可以用
CASE .. WHEN声明：UPDATE clients
SET online = (id IN(4,5,8,10,12))
WHERE
id BETWEEN 2 AND 15 AND parentId = 123;