logo标签列表

SQL - 一次查询中更新多条记录

mysqlrecord
198

我有一个名为config的表。

架构: config_name | config_value

我想要在一个查询中更新多条记录。我尝试了以下方法：

UPDATE config 
SET t1.config_value = 'value'
  , t2.config_value = 'value2' 
WHERE t1.config_name = 'name1' 
  AND t2.config_name = 'name2';

但是那个查询是错误的 :(

你能帮我吗？

2
你使用的是哪个数据库？ - Hart CO
1
我正在使用MySQL数据库。 - user3022527
1
可能从选择更新是答案。https://dev59.com/OnE95IYBdhLWcg3wY85l - Jonathan Benn
17个回答
228

尝试使用多表更新语法。

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';

这是一个 SQLFiddle 演示

或条件更新

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');

这里是一个SQLFiddle演示

6
好的，但是当我想在一次查询中更新16条记录时该怎么办？我应该使用16个JOIN吗？ - user3022527
39
你应该在问题的第一时间提及这些重要细节。无论如何，查看更新后的答案以获取另一种解决方案（有条件的更新）。 - peterm
3
在您的例子中，t1和t2是变量名，可能代表某种时间或计时器。 - Paweł Brewczynski
2
你好，@PaulBrewczynski。这些是表别名，可以写成 config AS t1，其中 AS 是可选的。 - peterm
1
@peterm：SQLFiddle链接已经失效。除此之外，条件更新技术运作得非常好。谢谢！ - Jonathan Benn
显示剩余2条评论
200
您可以通过以下 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);

这会向表中插入新值，但如果主键重复（已经插入到表中），那么您指定的值将被更新，并且相同的记录不会第二次插入。

39
一个相当聪明的黑客。我感到惊讶。 - Blaise
8
不支持在Postgres中使用，参见：https://dev59.com/uHNA5IYBdhLWcg3wC5Xh - kevzettler
2
此外，这也是将少量或大量CSV转换为带有一些小文本编辑的表格插入/更新/合并的绝佳方式！ - wulftone
12
这是一个MySQL的解决方案，不是Postgres或MSSQL。 - Rz Mk
9
即使记录没有被插入或更新，这也会增加自动增量ID。 - Timo Huovinen
显示剩余3条评论
33

使用这个替代

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';

你可以使用

UPDATE staff SET salary = 1200 WHERE name IN ('Bob', 'Frank', 'John');
3
我喜欢这个。 - dockeryZ
33

就我个人而言，我需要更新超过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是用户ID，我需要分别将base_id更新为999和88。这对我有用。

3
这是一个很棒的东西，对我非常有效。 - Shahrukh Anwar
这是一个非常惯用的解决方案，对我非常有效，谢谢！ - offerni
这个！我需要更新一些表的顺序，这个帮了我大忙。 - Fabricyo Figueira
11

对于某些人来说，这可能会很有用

对于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

这个SQL语句可以更新已存在的记录，同时如果是新记录则会插入（一举两得）

2
据我所见，根据您的查询，id是该表的主键。假设有2个或更多列被视为主键（复合键）...在这种情况下，检查冲突的正确方法应该是什么？ - Sritam Jagadev
9

Camille的解决方案有效。将其转换为基本的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;
    }
6
执行下面的代码来更新n行数据，其中Parent ID是你要获取数据的ID，Child IDs是需要更新的ID，因此只需将父ID和子ID添加到脚本中以更新所有所需的行。
 UPDATE [Table]
 SET column1 = (SELECT column1 FROM Table WHERE IDColumn = [PArent ID]),
     column2 = (SELECT column2 FROM Table WHERE IDColumn = [PArent ID]),
     column3 = (SELECT column3 FROM Table WHERE IDColumn = [PArent ID]),
     column4 = (SELECT column4 FROM Table WHERE IDColumn = [PArent ID]),
 WHERE IDColumn IN ([List of child Ids])
5

更新 2021 / MySql v8.0.20 及更高版本

最得票的答案建议使用 VALUES 函数，但该函数现在已经被 废弃 用于 ON DUPLICATE KEY UPDATE 语法。在 v8.0.20 版本中，使用 VALUES 函数会收到废弃警告：

INSERT INTO chart (id, flag)
VALUES (1, 'FLAG_1'),(2, 'FLAG_2')
ON DUPLICATE KEY UPDATE id = VALUES(id), flag = VALUES(flag);

【警告】'VALUES function'已被弃用且将在未来版本中删除。请使用别名（INSERT INTO ... VALUES (...) AS alias）并将ON DUPLICATE KEY UPDATE子句中的VALUES(col)替换为alias.col。

改用新的别名语法：

INSERT INTO chart (id, flag) 
VALUES (1, 'FLAG_1'),(2, 'FLAG_2') AS aliased
ON DUPLICATE KEY UPDATE flag=aliased.flag;
5
如果您想更新所有列中的所有记录，请执行以下代码：
update config set column1='value',column2='value'...columnN='value';

如果你想更新特定行的所有列，请执行以下代码：

update config set column1='value',column2='value'...columnN='value' where column1='value'
5
如果不同行有不同的值怎么办？例如： UPDATE staff SET salary = 1125 WHERE name = 'Bob'; UPDATE staff SET salary = 1200 WHERE name = 'Jane'; UPDATE staff SET salary = 1100 WHERE name = 'Frank'; UPDATE staff SET salary = 1175 WHERE name = 'Susan'; UPDATE staff SET salary = 1150 WHERE name = 'John'; - Abdullah Nurum
4

假设你有一个Excel电子表格，其中列A1中的值为config_value，列B1中的值为config_name，你可以使用类似Excel公式的方式编写查询语句：

=CONCAT("UPDATE config SET config_value = ","'",A1,"'", " WHERE config_name = ","'",B1,"'")

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接