更新具有不同值的多行 [英] Updating multiple rows with different values

问题描述

我在MySQL数据库用户"中获得了该表.它具有字段"id"和"value".

I got this table in my MySQL database, 'users'. It has the fields 'id' and 'value'.

现在，我想使用单个 SQL查询更新此表中的很多行，但是许多行应获得不同的值.目前，我正在使用此:

Now, I want to update lots of rows in this table with a single SQL query, but many rows should get a different value. Currently, I'm using this:

UPDATE users
    SET value = CASE id
        WHEN 1 THEN 53
        WHEN 2 THEN 65
        WHEN 3 THEN 47
        WHEN 4 THEN 53
        WHEN 5 THEN 47
    END
WHERE id IN (1,2,3,4,5)

这有效.但是我觉得我可以做一些优化，因为我只为行分配了3或4个不同的值.如您所见，现在分别是47、53和65.有没有一种方法可以更新在同一查询中同时获得相同值的所有行?或者，还有另一种方法可以优化此效果吗?

This works. But I feel I could do some optimization since there are only about 3 or 4 different values I'm assigning to the rows. As you can see, right now these are 47, 53 and 65. Is there a way I can update all rows that get the same value simultaneously within the same query? Or, is there another way I can optimize this?

推荐答案

与其做case variable when value then ...，不如做case when condition then ...-像这样:

Rather than doing case variable when value then ..., try doing case when condition then ... - like so:

UPDATE users
    SET value = CASE 
        WHEN id in (1,4) THEN 53
        WHEN id = 2 THEN 65
        WHEN id in (3,5) THEN 47
    END
WHERE id IN (1,2,3,4,5)

这篇关于更新具有不同值的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！