问题描述
以下 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