MySQL - Update multiple values and 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

just use serialize() function to convert array to string and then write it to name column, and when you read data user unserialize() to convert string to array — Aleksandar Vasić, Aug 16 '14 at 12:29
You misunderstand my question, I don't want to run SQL multiple times — user3896501, Aug 16 '14 at 12:31

score 2 · Accepted Answer · answered Aug 16 '14 at 12:35

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.

score 0 · Answer 2 · answered Aug 16 '14 at 12:31

0

Assuming the question isn't oversimplifying the real issue you have, you could use the concat function:

UPDATE files
SET    name = CONCAT('untitled', id)
WHERE  id IN (1,2,3,4)

answered Aug 16 '14 at 12:31

Mureinik

297,002
52
306
350

how about something like `'abcd', 'efgh', 'ijkl'` – user3896501 Aug 16 '14 at 12:34

MySQL - Update multiple values and WHERE IN

2 Answers2

Linked

Related