Mysql批量update

内容转自博客：https://www.awaimai.com/2103.html

更新一条数据

UPDATE mytable SET myfield = 'value' WHERE other_field = 'other_value';

将多条数据的某一字段更新为相同值

UPDATE mytable SET myfield = 'value' WHERE other_field in ('other_values');

多条数据更新为不同值

UPDATE mytable SET
    myfield = CASE id
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3)

多条数据更新多个字段

UPDATE categories SET
    display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)