在两行数据之间交换值

问题描述

以下 T-SQL 代码段有效，但我想知道是否有更聪明和/或更简洁的方法来完成两个不同行之间的字段值交换.(为简单起见，代码具有硬编码的 pkey 值.)

The following T-SQL code segment works but i'm wondering if there's a cleverer and/or less verbose way to accomplish the swapping of field values between two different rows. (The code has hard-coded pkey values for simplicity.)

BEGIN TRAN;

declare @swapFormSeqA int;
declare @swapFormSeqB int;

SELECT @swapFormSeqA = DisplaySeq
FROM CustomCatalogForm
WHERE CustomCatalogFormId = 1;

SELECT @swapFormSeqB = DisplaySeq
FROM CustomCatalogForm
WHERE CustomCatalogFormId = 2;

UPDATE CustomCatalogForm
SET DisplaySeq = @swapFormSeqB
WHERE CustomCatalogFormId = 1;

UPDATE CustomCatalogForm
SET DisplaySeq = @swapFormSeqA
WHERE CustomCatalogFormId = 2;

COMMIT TRAN;

我专门使用 Sql2k5，所以 2** 不是一个选项.

i'm using Sql2k5 specifically so 2** is not an option.

推荐答案

你可以这样做:

begin tran

declare @sum int

select @sum = sum(DisplaySeq)
from CustomCatalogForm
where CustomCatalogFormId in (1,2)

update CustomCatalogForm
set DisplaySeq = @sum - DisplaySeq
where CustomCatalogFormId in (1,2)

commit tran