问题描述
我在我的 MySQL 数据库用户"中获得了这张表.它具有id"和value"字段.
I got this table in my MySQL database, 'users'. It has the fields 'id' and 'value'.
现在，我想用 single 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)
这篇关于用不同的值更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！