Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Updating multiple rows with different values

Tags:

I got this table in my MySQL database, 'users'. It has the fields 'id' and 'value'.

Now, I want to update lots of rows in this table with a single SQL query, but many rows should get a different value. Currently, I'm using this:

UPDATE users
    SET value = CASE id
        WHEN 1 THEN 53
        WHEN 2 THEN 65
        WHEN 3 THEN 47
        WHEN 4 THEN 53
        WHEN 5 THEN 47
    END
WHERE id IN (1,2,3,4,5)

This works. But I feel I could do some optimization since there are only about 3 or 4 different values I'm assigning to the rows. As you can see, right now these are 47, 53 and 65. Is there a way I can update all rows that get the same value simultaneously within the same query? Or, is there another way I can optimize this?