在一行SQL中更新两个不同的行 [英] Update two different rows in one line of SQL

问题描述

说我有一个名为example的表，

Say I have a table called example as:

[abc] | [def]

[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

OR

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中更新两个不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！