使用 where 语句批量更新 mysql

2022-01-17 00:00:00 sql-update mysql bulk

如何批量更新mysql数据?如何定义这样的东西:

How to update mysql data in bulk ? How to define something like this :

UPDATE `table` 
WHERE `column1` = somevalues
SET  `column2` = othervalues

带有一些值，例如:

VALUES
    ('160009'),
    ('160010'),
    ('160011');

和其他价值观:

VALUES
    ('val1'),
    ('val2'),
    ('val3');

也许用 mysql 是不可能的?一个php脚本?

maybe it's impossible with mysql ? a php script ?

推荐答案

在您的情况下，最简单的解决方案是使用 ON DUPLICATE KEY UPDATE 构造.它的工作速度非常快，并且可以轻松完成工作.

The easiest solution in your case is to use ON DUPLICATE KEY UPDATE construction. It works really fast, and does the job in easy way.

INSERT into `table` (id, fruit)
    VALUES (1, 'apple'), (2, 'orange'), (3, 'peach')
    ON DUPLICATE KEY UPDATE fruit = VALUES(fruit);

或使用 CASE 建设

or to use CASE construction

UPDATE table
SET column2 = (CASE column1 WHEN 1 THEN 'val1'
                 WHEN 2 THEN 'val2'
                 WHEN 3 THEN 'val3'
         END)
WHERE column1 IN(1, 2 ,3);

相关文章