MySQL - 更新多个值和WHERE IN

Question

Is there any syntax like WHERE IN allow me to update multiple values at once? example:

update files
set name = 'untitled' 
WHERE id IN (1,2,3,4) 

become:

update files
set name ( 'untitled', 'untitled2', 'untitled3', 'untitled4' )
WHERE id IN (1,2,3,4)

My script contains an associative array, I need to update the name column set as array value where the id column match the array key

Answer 1

Are you looking for a case statement?

update files
    set name = (case when id = 1 then 'untitled'
                     when id = 2 then 'untitled2'
                     when id = 3 then 'untitled3'
                     when id = 4 then 'untitled4'
                end)
    where id IN (1, 2, 3, 4);

In MySQL, you can also do this with a join:

update files f join
       (select 1 as id, 'untitled' as newname union all
        select 2, 'untitled2' union all
        select 3, 'untitled3' union all
        select 4, 'untitled4'
       ) n
       on f.id = n.id
    f.name = new.newname;

If you have a lot of values, you can create a table with the values separately and then do the update.