在一行SQL中更新两个不同的行

Say I have a table called example as:

比方说，我有一个表，叫做

[abc] | [def]

(abc)|(def)

--1---|-qwerty-

- - - 1 - - - - - - | qwerty -

--2---|-asdf---

,2 - - - - - - | asdf - - - - - -

What I am wanting to do is update both columns in one SQL query (using only one UPDATE).

我要做的是在一个SQL查询中更新两个列(只使用一个更新)。

UPDATE example SET def = 'foo' where abc = '1'

UPDATE example SET def = 'bar' where abc = '2'

The above is what I am wanting to achieve but in one line of sql (using MySQL). I know you can do this like UPDATE example SET def 'foo', SET def = 'bar' but I'm not sure how you can do this with two different where statements.

上面是我想要实现的，但是只需要一行sql(使用MySQL)。我知道你可以这样做，比如更新例子，SET def 'foo'， SET def = 'bar'但是我不知道如何用两个不同的where语句。

2 个解决方案

#1

You can execute one UPDATE with the use of IF (which mysql supports) or by using CASE to make it more RDBMS friendly.

您可以使用IF (mysql支持的)或使用CASE来执行一次更新，使其更加RDBMS友好。

UPDATE  example
SET     def = IF(abc = 1, 'foo', 'bar')
WHERE   abc IN (1, 2) -- reason to make it more faster, doesn't go on all records

或

UPDATE  example
SET     def = CASE WHEN abc = 1 THEN 'foo' ELSE 'bar' END
WHERE abc IN (1, 2) -- reason to make it more faster, doesn't go on all records

#2

Like so:

像这样:

UPDATE example SET def = 
    CASE abc 
        WHEN '1' THEN 'foo' 
        WHEN '2' THEN 'bar'
    END

This allows you to enter more than 2 cases.

这允许您输入两个以上的情况。

#1