MySQL SQL - 在一个查询中更新多条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20255138/
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
SQL - Update multiple records in one query
提问by user3022527
I have table - config.
Schema:
config_name | config_value
我有表配置。架构：
config_name | config_value
And I would like to update multiple records in one query. I try like that:
我想在一个查询中更新多条记录。我这样尝试：
UPDATE config
SET t1.config_value = 'value'
, t2.config_value = 'value2'
WHERE t1.config_name = 'name1'
AND t2.config_name = 'name2';
but that query is wrong :(
但该查询是错误的:(
Can you help me?
你能帮助我吗？
回答by peterm
Try either multi-table update syntax
尝试多表更新语法
UPDATE config t1 JOIN config t2
ON t1.config_name = 'name1' AND t2.config_name = 'name2'
SET t1.config_value = 'value',
t2.config_value = 'value2';
Here is SQLFiddledemo
这是SQLFiddle演示
or conditional update
或有条件更新
UPDATE config
SET config_value = CASE config_name
WHEN 'name1' THEN 'value'
WHEN 'name2' THEN 'value2'
ELSE config_value
END
WHERE config_name IN('name1', 'name2');
Here is SQLFiddledemo
这是SQLFiddle演示
回答by camille khalaghi
You can accomplish it with INSERT as below:
您可以使用 INSERT 完成它，如下所示：
INSERT INTO mytable (id, a, b, c)
VALUES (1, 'a1', 'b1', 'c1'),
(2, 'a2', 'b2', 'c2'),
(3, 'a3', 'b3', 'c3'),
(4, 'a4', 'b4', 'c4'),
(5, 'a5', 'b5', 'c5'),
(6, 'a6', 'b6', 'c6')
ON DUPLICATE KEY UPDATE id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c);
This insert new values into table, but if primary key is duplicated (already inserted into table) that values you specify would be updated and same record would not be inserted second time.
这将新值插入表中，但如果主键重复（已插入表中），您指定的值将被更新，并且不会第二次插入相同的记录。
回答by vaibhav kulkarni
in my case I have to update the records which are more than 1000, for this instead of hitting the update query each time I preferred this,
在我的情况下，我必须更新超过 1000 条的记录，而不是每次我喜欢这个时都点击更新查询，
UPDATE mst_users
SET base_id = CASE user_id
WHEN 78 THEN 999
WHEN 77 THEN 88
ELSE base_id END WHERE user_id IN(78, 77)
78,77 are the user Ids and for those user id I need to update the base_id 999 and 88 respectively.This works for me.
78,77 是用户 ID，对于那些用户 ID，我需要分别更新 base_id 999 和 88。这对我有用。
回答by Oleg Sobchuk
maybe for someone it will be useful
也许对某人有用
for Postgresql 9.5 works as a charm
对于 Postgresql 9.5 很有魅力
INSERT INTO tabelname(id, col2, col3, col4)
VALUES
(1, 1, 1, 'text for col4'),
(DEFAULT,1,4,'another text for col4')
ON CONFLICT (id) DO UPDATE SET
col2 = EXCLUDED.col2,
col3 = EXCLUDED.col3,
col4 = EXCLUDED.col4
this SQL updates existing record and inserts if new one (2 in 1)
此 SQL 更新现有记录并插入新记录（二合一）
回答by adamk
Camille's solution worked. Turned it into a basic PHP function, which writes up the SQL statement. Hope this helps someone else.
卡米尔的解决方案奏效了。把它变成了一个基本的 PHP 函数，它写了 SQL 语句。希望这对其他人有帮助。
function _bulk_sql_update_query($table, $array)
{
/*
* Example:
INSERT INTO mytable (id, a, b, c)
VALUES (1, 'a1', 'b1', 'c1'),
(2, 'a2', 'b2', 'c2'),
(3, 'a3', 'b3', 'c3'),
(4, 'a4', 'b4', 'c4'),
(5, 'a5', 'b5', 'c5'),
(6, 'a6', 'b6', 'c6')
ON DUPLICATE KEY UPDATE id=VALUES(id),
a=VALUES(a),
b=VALUES(b),
c=VALUES(c);
*/
$sql = "";
$columns = array_keys($array[0]);
$columns_as_string = implode(', ', $columns);
$sql .= "
INSERT INTO $table
(" . $columns_as_string . ")
VALUES ";
$len = count($array);
foreach ($array as $index => $values) {
$sql .= '("';
$sql .= implode('", "', $array[$index]) . "\"";
$sql .= ')';
$sql .= ($index == $len - 1) ? "" : ", \n";
}
$sql .= "\nON DUPLICATE KEY UPDATE \n";
$len = count($columns);
foreach ($columns as $index => $column) {
$sql .= "$column=VALUES($column)";
$sql .= ($index == $len - 1) ? "" : ", \n";
}
$sql .= ";";
return $sql;
}
回答by Shuhad zaman
instead of this
而不是这个
UPDATE staff SET salary = 1200 WHERE name = 'Bob';
UPDATE staff SET salary = 1200 WHERE name = 'Jane';
UPDATE staff SET salary = 1200 WHERE name = 'Frank';
UPDATE staff SET salary = 1200 WHERE name = 'Susan';
UPDATE staff SET salary = 1200 WHERE name = 'John';
you can use
您可以使用
UPDATE staff SET salary = 1200 WHERE name IN ('Bob', 'Frank', 'John');
回答by Harrish
Execute the code below to update n number of rows, where Parent ID is the id you want to get the data from and Child ids are the ids u need to be updated so it's just u need to add the parent id and child ids to update all the rows u need using a small script.
执行下面的代码以更新 n 行，其中父 ID 是您要从中获取数据的 ID，子 ID 是您需要更新的 ID，因此您只需要添加父 ID 和子 ID 即可更新您需要使用小脚本的所有行。
UPDATE [Table]
SET couloumn1= (select couloumn1 FROM Table WHERE IDCouloumn = [PArent ID]),
couloumn2= (select couloumn2 FROM Table WHERE IDCouloumn = [PArent ID]),
couloumn3= (select couloumn3 FROM Table WHERE IDCouloumn = [PArent ID]),
couloumn4= (select couloumn4 FROM Table WHERE IDCouloumn = [PArent ID]),
WHERE IDCouloumn IN ([List of child Ids])
回答by Ivar
Assuming you have the list of values to update in an Excel spreadsheet with config_valuein column A1and config_namein B1you can easily write up the query there using an Excel formula like
假设你具有的值的列表来更新在Excel电子表格与CONFIG_VALUE列A1和CONFIG_NAME在B1可以很容易地写出了查询有使用Excel式像
=CONCAT("UPDATE config SET config_value = ","'",A1,"'", " WHERE config_name = ","'",B1,"'")
=CONCAT("UPDATE config SET config_value = ","'",A1,"'", " WHERE config_name = ","'",B1,"'")
回答by Jason Clark
Execute the below code if you want to update all record in all columns:
如果要更新所有列中的所有记录，请执行以下代码：
update config set column1='value',column2='value'...columnN='value';
and if you want to update all columns of a particular row then execute below code:
如果要更新特定行的所有列，请执行以下代码：
update config set column1='value',column2='value'...columnN='value' where column1='value'
