mysql update case update field value is not fixed operation

When processing batch updates of certain data, if the values ​​of the fields you are updating are the same, such as a certain status is updated to a fixed value,

Just use update table set xxx=xxx where xxx=xxx

If the value of the field to be updated is not fixed, it is more convenient to use the following update case when where method:

UPDATE tablename 
set a1= CASE userid 
 WHEN 1 THEN a1+5 
 WHEN 2 THEN a1+2 
 END,
a2= CASE userid 
 WHEN 1 THEN a2-5 
 WHEN 2 THEN a2-2
END
where userid in (1,2)

When using update case when, you must include the where condition, otherwise the entire table will be updated, which can have serious consequences.

Supplement: MySQL update && case when combined with batch update

Direct code sql:

The room field value is in the format of 18F-N01

UPDATE t_report SET room = CONCAT_WS( '-', SUBSTRING_INDEX(room, '-', 1) ,
 CASE SUBSTRING_INDEX(room, '-', -1) 
 WHEN 'N01' THEN 'N02' 
 WHEN 'N02' THEN 'N01'
 WHEN 'N03' THEN 'N11'
 WHEN 'N04' THEN 'N10'
 WHEN 'N05' THEN 'N09'
 WHEN 'N06' THEN 'N08'
 WHEN 'N07' THEN 'N07'
 WHEN 'N08' THEN 'N06'
 WHEN 'N09' THEN 'N05'
 WHEN 'N10' THEN 'N03'
 END )
WHERE rid IN ( 
 SELECT rid FROM t_report WHERE rdate = '190306' AND ordinal BETWEEN '23' AND '32'
)

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.