sql-server - 如何使用一条 UPDATE 语句更新两列值，反之亦然

Question

我刚从我的 friend 那里得到这个问题。我有一张如下所示的表格

DECLARE @table TABLE
(
ID INT IDENTITY(1,1) NOT NULL,
NAME VARCHAR(30),
SURNAME VARCHAR(30)
)

INSERT INTO @table(NAME, SURNAME)
VALUES('Mohit', 'Vaghadiya'),
      ('Paresh', 'Panchal'),
      ('Devid','Pietersen'),
      ('kapil','Soni')

===================================
| ID  |     NAME    |   SURNAME   |    
=================================
| 1   |   Mohit     |   Vaghadiya |
| 2   |   Paresh    |   Panchal   |
| 3   |   Devid     |   Pietersen |
| 4   |   kapil     |   Soni      |
===================================

我只想用 ID '3 的 SURNAME 更新 ID '1' 的 NAME 列值' 反之亦然，我想用 ID 的 NAME 更新 ID '3' 的 SURNAME 列值 ' 1'

输出将是

===================================
| ID  |     NAME    |   SURNAME   |    
===================================
| 1   |   Pietersen |   Vaghadiya |
| 2   |   Paresh    |   Panchal   |
| 3   |   Devid     |   Mohit     |
| 4   |   kapil     |   Soni      |
===================================

我知道我们可以这样做

DECLARE @name VARCHAR(30)

SELECT @name = SURNAME FROM @table WHERE ID = 3

UPDATE @table
SET SURNAME = (SELECT NAME FROM @table WHERE ID = 1)
WHERE ID = 3

UPDATE @table
SET NAME = @name
WHERE ID = 1

但是有没有可能通过只执行一个UPDATE语句来做到这一点？

Answer 1

您可以使用两个 case表达式，一个用于名字，另一个用于姓氏:

UPDATE @table
  SET name =
       case
         when ID = 1 then (SELECT SURNAME FROM @table WHERE ID = 3)
         else name
       end,

     surname =
       case
         when ID = 3 then (SELECT NAME FROM @table WHERE ID = 1)
         else surname
       end
WHERE ID in (1, 3)

sqlFiddle