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

本文介绍了在一行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