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

问题描述

假设我有一个名为 example 的表:

Say I have a table called example as:

[abc] |[定义]

[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执行一个UPDATE(mysql支持)em>) 或使用 CASE 使其对 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 中更新两个不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！