MySQL案例来更新多个列。

I would like to update multiple columns in my table using a case statement, but I cannot find how to do this (is this even possible). I came up with the following invalid reference query:

我想使用case语句更新表中的多个列，但我找不到如何进行此操作(是否可能)。我提出了以下无效引用查询:

UPDATE tablename SET
    CASE name
        WHEN 'name1' THEN col1=5,col2=''
        WHEN 'name2' THEN col1=3,col2='whatever'
        ELSE col1=0,col2=''
    END;

Is there any way of achieving the expected result with valid SQL?

是否有任何方法可以通过有效的SQL实现预期的结果?

3 个解决方案

#1

UPDATE tablename
SET col1 = CASE WHEN name = 'name1' THEN 5 
                WHEN name = 'name2' THEN 3 
                ELSE 0 
           END
 , col2 = CASE WHEN name = 'name1' THEN '' 
               WHEN name = 'name2' THEN 'whatever' 
               ELSE '' 
          END
;

#2

I don't know of any clean way to do what you're asking. An equivalent valid SQL update would be:

我不知道有什么干净的方法可以做你想问的。一个等效的有效SQL更新是:

UPDATE tablename SET
    col1 = CASE name WHEN 'name1' THEN 5 WHEN 'name2' THEN 3 ELSE 0 END,
    col2 = CASE name WHEN 'name1' THEN '' WHEN 'name2' THEN 'whatever' ELSE '' END;

Of course this isn't pretty and requires repeating the same cases (e.g. 'name1') multiple times, but I just don't think it's possible any other way.

当然这并不好看，需要重复同样的情况。“name1”)很多次了，但是我不认为有其他的方法。

#3

If name has a unique index and your values are known to exist in the table, you can use this trick:

如果名称有一个唯一的索引，并且您的值已知存在于表中，您可以使用以下技巧:

INSERT INTO tablename (name, col1, col2)
VALUES ('name1', 5, '')
     , ('name2', 3, 'whatever')
ON DUPLICATE KEY UPDATE
       col1 = VALUES(col1)
     , col2 = VALUES(col2);

If there are any additional NOT NULL columns without a default value, you'll have to add dummy values for those. Just leave them out of the ON DUPLICATE KEY UPDATE and they'll be ignored.

如果有任何没有默认值的非空列，则必须为这些列添加假值。只要将它们从ON DUPLICATE KEY UPDATE中删除，它们就会被忽略。

#1

UPDATE tablename
SET col1 = CASE WHEN name = 'name1' THEN 5 
                WHEN name = 'name2' THEN 3 
                ELSE 0 
           END
 , col2 = CASE WHEN name = 'name1' THEN '' 
               WHEN name = 'name2' THEN 'whatever' 
               ELSE '' 
          END
;

#2