在一行 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
相关文章