How to combine the below the update querys into one, Update multiple rows with multiple values for s-CodePudding

here i want to change the date for 9 rows

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)

The change the status

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

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)

There are about 52 more Ids for whom I was to set the status to a different value which would look like below.

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

CodePudding user response：

One way is using multiple case conditions:

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;

CodePudding user response：

Use multiple-table 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 contains all data needed for definite updating.

COALESCE provides storing _When_Updated value and setting definite _Status value for the rows with _id values which are not listed in data_for_update.

You may add some WHERE conditions when not all rows in forum_topic_resume not listed in data_for_update must be updated with new _Status value.

Of course the query text will be long, but the execution will be fast enough.