Can this be run as a single update query?

Question

UPDATE userTable 
SET userAge=245, userName="fred"  WHERE userId = 321, 
SET userAge=32, userName="dave" WHERE userId = 424;

Is there a better way to write this code?

You should use multiple `UPDATE` statements for this. Your syntax is not standard. — sangaran, Aug 06 '13 at 13:53
Possible duplicate - http://stackoverflow.com/questions/3432/multiple-updates-in-mysql — mara-mfa, Aug 06 '13 at 14:02

Gordon Linoff · Accepted Answer · 2013-08-06T16:04:50.730

Yes, using case statements:

UPDATE userTable 
    SET userAge= (case when userId = 321 then 245 else 32 end),
        userName= (case when userId = 321 then 'fred' else 'dave' end)
    WHERE userId in (321, 424);

However, I think a more general way to write this is using join syntax:

UPDATE userTable join
       (select 321 as UserId, 'fred' as userName, 245 as userAge union all
        select 424, 'dave', 32
       ) toupdate
       on userTable.userId = toupdate.UserId
    set userTable.userAge = toupdate.userAge,
        userTable.userName = toupdate.userName;

This makes it easier to add more rows, and shows the power of using join with update.

EDIT:

About performance. Two updates require setting up two transactions in the database; one update requires only one. So, one update is likely to be a wee bit faster. The performance difference would only be noticeable if you had no index on userTable(userId). With such an index, both versions (with the where clause and using join) should use the index to find the rows to update quite quickly.

But, there is a more important difference. Two updates leave the table in an inconsistent state "between" the updates -- the user ids and names won't be consistent between these updates. If the second one fails or someone uses the table, they'll have inconsistent data. You want to do the two updates at the same time (you could also fix this by using explicit transactions, but why bother?).

While this is technically correct, the maintainability of this query just jumped off a cliff. — tadman, Aug 06 '13 at 14:31
@tadman . . . I'm not sure which version you are referring to. I find the second version *much* more maintainable. In particular, the relationship between the values for a given user is quite clear, because they are all on one row. — Gordon Linoff, Aug 06 '13 at 14:48
Thanks @Gordon, your first solution appears to be the general answer by everyone, would this actually be quicker to run than multiple updates?regards J — jon, Aug 06 '13 at 15:08
The second version is still a disaster. This isn't readable, it's not more concise than two simple `UPDATE` statements, and it'll only get worse when you add more conditions. How about fifteen different updates? It'll be totally out of control. — tadman, Aug 06 '13 at 15:09

score 5 · Answer 2 · answered Aug 06 '13 at 13:53

5

UPDATE userTable 
SET userAge =  case when userId = 321 then 245
                    when userId = 424 then 32
               end,
    userName = case when userId = 321 then "fred"
                    when userId = 424 then "dave"   
               end      
WHERE userId in (321, 424)

answered Aug 06 '13 at 13:53

juergen d

201,996
37
293
362

thanks for the answer but I think Gordon answered first.. Would this actually be faster than multiple updates, if say I was updating 100 rows? thanks again J – jon Aug 06 '13 at 15:11

score 2 · Answer 3 · answered Aug 06 '13 at 13:54

2

One way of doing it would be

UPDATE userTable 
SET userAge=(case when userId=321 then 245 else 424 end),
    userName=(case when userId=321 then 'fred' else 'dave' end)
WHERE userId in (321,, 424)

although doing it with two queries is also fine.

answered Aug 06 '13 at 13:54

Aleks G

56,435
29
168
265

score 2 · Answer 4 · answered Aug 06 '13 at 13:54

use case STATMENTS instead.

UPDATE userTable 
    SET userAge =  case when userId = 321  then 245
                        when userId = 424  then 32     end,
       userName = case  when userId = 321  then "fred"
                        when userId = 424  then "dave"  end      
     WHERE userId in (321, 424)

score 2 · Answer 5 · answered Aug 06 '13 at 13:59

2

My solution to combine many UPDATE-querys like this is INSERT INTO... ON DUPLICATE KEY UPDATE. So if userId is primary key you can use

INSERT INTO userTable (userId, userAge, userName) VALUES
(321,245,"fred"),(424,32,"dave")
ON DUPLICATE KEY UPDATE userAge = VALUES(userAge), userName = VALUES(userName);

answered Aug 06 '13 at 13:59

Markus Madeja

848
7
10

This is the one I use most often, but you need to be careful if your update violates other UNIQUE inexes - UPDATE will throw an error in those cases. – Vatev Aug 06 '13 at 14:15

Can this be run as a single update query?

5 Answers5