mysql 中更新查询中的多个 set 和 where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5773422/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple set and where clauses in Update query in mysql
提问by Boardy
I don't think this is possible as I couldn't find anything but I thought I would check on here in case I am not searching for the correct thing.
我不认为这是可能的，因为我找不到任何东西，但我想我会在这里检查，以防我没有寻找正确的东西。
I have a settings table in my database which has two columns. The first column is the setting name and the second column is the value.
我的数据库中有一个设置表，它有两列。第一列是设置名称，第二列是值。
I need to update all of these at the same time. I wanted to see if there was a way to update these values at the same time one query like the following
我需要同时更新所有这些。我想看看是否有办法在一个查询的同时更新这些值，如下所示
UPDATE table SET col1='setting name' WHERE col2='1 value' AND SET col1='another name' WHERE col2='another value';
I know the above isn't a correct SQL format but this is the sort of thing that I would like to do so was wondering if there was another way that this can be done instead of having to perform separate SQL queries for each setting I want to update.
我知道以上不是正确的 SQL 格式，但这是我想要做的事情，想知道是否有另一种方法可以完成此操作，而不必为我想要的每个设置执行单独的 SQL 查询更新。
Thanks for your help.
谢谢你的帮助。
采纳答案by Boardy
I decided to use multiple queries all in one go. so the code would go like
我决定一次性使用多个查询。所以代码会像
UPDATE table SET col2='value1' WHERE col1='setting1';
UPDATE table SET col2='value2' WHERE col1='setting1';
etc etc
等等等等
I've just done a test where I insert 1500 records into the database. Do it without starting a DB transaction and it took 35 seconds, blanked the database and did it again but starting a transaction first, then once the 1500th record inserted finish the transaction and the time it took was 1 second, so definetely seems like doing it in a db transaction is the way to go.
我刚刚做了一个测试，我在数据库中插入了 1500 条记录。在不启动数据库事务的情况下执行它花了 35 秒，将数据库清空并再次执行但首先启动事务，然后一旦插入第 1500 条记录完成事务，并且花费的时间为 1 秒，所以肯定看起来像这样做在数据库事务中是要走的路。
回答by Halcyon
You can use INSERT INTO .. ON DUPLICATE KEY UPDATEto update multiple rows with different values.
您可以使用INSERT INTO .. ON DUPLICATE KEY UPDATE不同的值更新多行。
You do need a unique index (like a primary key) to make the "duplicate key"-part work
您确实需要一个唯一索引（如主键）来使“重复键”部分工作
Example:
例子：
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c);
-- VALUES(x) points back to the value you gave for field x
-- so for b it is 2 and 5, for c it is 3 and 6 for rows 1 and 4 respectively (if you assume that a is your unique key field)
If you have a specific case I can give you the exact query.
如果您有特定情况，我可以为您提供确切的查询。
回答by sll
UPDATE table
SET col2 =
CASE col1
WHEN 'setting1'
THEN 'value'
ELSE col2
END
, SET col1 = ...
...
回答by Jegatheesh
@Frits Van Campen,
@Frits Van Campen，
The insert into .. on duplicate works for me. I am doing this for years when I want to update more than thousand records from an excel import.
插入到 .. 重复对我有用。当我想从 excel 导入更新超过一千条记录时，我已经这样做了多年。
Only problem with this trick is, when there is no record to update, instead of ignoring, this method inserts a record and on some instances it is a problem. Then I need to insert another field, then after import I have to delete all the records that has been inserted instead of update.
这个技巧的唯一问题是，当没有要更新的记录时，此方法不是忽略而是插入记录，在某些情况下这是一个问题。然后我需要插入另一个字段，然后在导入后我必须删除所有已插入的记录而不是更新。
回答by Pentium10
You need to run separate SQL queries and make use of Transactions if you want to run as atomic.
如果您想以原子方式运行，则需要运行单独的 SQL 查询并使用事务。
回答by piotrm
UPDATE table SET col1=if(col2='1 value','setting name','another name') WHERE col2='1 value' OR col2='another value'
