可以将其作为单个更新查询运行吗?

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?

有没有更好的方法来编写这段代码?

5 个解决方案

#1

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:

但是，我认为更一般的写法是使用join语法:

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.

这样可以更容易地添加更多的行，并显示使用join与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.

关于性能。两个更新需要在数据库中设置两个事务;一个更新只需要一个。因此，一次更新可能会快一点。性能差异只有在userTable(userId)上没有索引时才会明显。对于这样的索引，两个版本(包含where子句和使用join)都应该使用索引查找要快速更新的行。

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?).

但是，还有一个更重要的区别。两个更新使表处于不一致状态“在”更新之间——用户id和名称在这些更新之间不一致。如果第二个失败，或者有人使用该表，则会有不一致的数据。您希望同时执行这两个更新(您也可以通过使用显式事务来修复这一点，但是为什么要这么麻烦呢?)

#2

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)

#3

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.

尽管用两个查询来做也可以。

#4

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)

#5

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

我的解决方案是将许多这样的更新查询合并到……在重复键更新。如果userId是你可以使用的主键

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

#1