在单个查询中用不同的值更新多行-MySQL [英] Update multiple rows with different values in a single query - MySQL

问题描述

我是MySQL的新手.

I'm new to MySQL.

我正在使用它来在单个查询中更新具有不同值的多行:

UPDATE categories
    SET order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

我正在使用"WHERE"来提高性能(如果没有测试，表中的每一行都将被测试).

I am using "WHERE" to improve performance (without it every row in the table would be tested).

但是，如果我有此Senario(当我不想更新ID 2和3的标题时)，该怎么办:

UPDATE categories
    SET order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
    END
WHERE id IN (1,2,3)

上面的代码会将ID 2和ID 3的标题更改为"NULL" ...

The above code will change the title for id 2 and 3 into "NULL"...

什么是正确的查询方式，但是跳过更新ID 2和ID 3的标题，并且仍然保持性能"WHERE ID IN"所赋予的作用?

也许是这样

UPDATE categories
    SET order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN  THEN 
        WHEN  THEN 
    END
WHERE id IN (1,2,3)

推荐答案

当您不想将其更新为其他值时，请将标题设置为与自身相同.

Set title equal to itself when you don't want to update it to a different value.

UPDATE categories
    SET order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        ELSE title
    END
WHERE id IN (1,2,3)

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