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

问题描述：

说我有一个名为example的表，

Say I have a table called example as:

[abc] | [def]

-1 --- || -qwerty-

--1---|-qwerty-

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

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

我想要做的是更新一个SQL查询中的两列(仅使用一个UPDATE).

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

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

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

以上是我要实现的内容，但是在一行sql中(使用MySQL).我知道您可以像UPDATE example SET def 'foo', SET def = 'bar'这样执行此操作，但是我不确定如何使用两个不同的where语句来完成此操作.

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.

答

您可以使用IF(mysql支持的 )或使用CASE执行一个UPDATE使它对RDBMS更加友好.

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

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

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

相关推荐