使用where语句批量更新mysql [英] Bulk update mysql with where statement

问题描述

如何批量更新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 ?

推荐答案

在您的情况下，最简单的解决方案是使用

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 构造

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

这篇关于使用where语句批量更新mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！