如何将下面的更新查询合并为一个，对同一列使用多个值更新多行，其余数据使用其他值更新

Question

这里我想更改9行的日期

1。

UPDATE forum_topic_resume 
SET _When_Updated = (now() -INTERVAL 6 day) 
WHERE _id IN (96250, 69081, 69555)

UPDATE forum_topic_resume 
SET _When_Updated = (now() -INTERVAL 8 day) 
WHERE _id IN (70494, 68612, 69564, 69660, 72437, 80498)

改变状态

3。

UPDATE forum_topic_resume 
SET _Status = 1224 
WHERE _id IN (96250, 69081, 69555)

4

UPDATE forum_topic_resume 
SET _Status_Is = 1228 
WHERE _id IN (70494, 68612, 69564)

UPDATE forum_topic_resume 
SET _Status_Is = 1229 
WHERE _id IN (69660, 72437, 80498)

还有大约52个id，我要将它们的状态设置为不同的值，如下所示。

6。

UPDATE forum_topic_resume 
SET _Status_Is = 1250 
WHERE _id IN (for the rest of the ids)

Answer 1

一种方法是使用多大小写条件:

UPDATE forum_topic_resume  
SET  _When_Updated  =  CASE 
WHEN _id IN (96250, 69081, 69555) THEN (now() -INTERVAL 6 day)
WHEN _id IN (70494, 68612, 69564, 69660, 72437, 80498) THEN (now() -INTERVAL 8 day)
other id conditions  ............. 
END,
_Status  = CASE             
WHEN _id IN (96250, 69081, 69555) THEN 1224 
WHEN _id IN (70494, 68612, 69564) 1228 
......
END,
_Status_Is = CASE           
WHEN _id IN (96250, 69081, 69555) THEN 1224 
WHEN _id IN (70494, 68612, 69564) 1228 
......
END;  

Answer 2

使用多表UPDATE

UPDATE forum_topic_resume 
LEFT JOIN (
SELECT 96250                  AS _id, 
now() - INTERVAL 6 day AS _When_Updated,
1224                   AS _Status
UNION ALL
SELECT 70494, now() -INTERVAL 8 day, 1228
UNION ALL
...
) data_for_update USING (_id)
SET forum_topic_resume._When_Updated = COALESCE(data_for_update._When_Updated,
forum_topic_resume._When_Updated),
forum_topic_resume._Status= COALESCE(data_for_update._Status,
1250);   

data_for_update包含确定更新所需的所有数据。

COALESCE为data_for_update中没有列出的_id值的行提供了_When_Updated值的存储和_Status值的确定。

当data_for_update中没有列出的forum_topic_resume中的所有行都必须更新为新的_Status值时，您可以添加一些WHERE条件。

当然，查询文本会很长，但执行速度会足够快。