Swap values between two rows of data

问题:

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;

EDIT: i'm using Sql2k5 specifically so 2k8 is not an option.

回答1:

You could do like this:

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

回答2:

Assuming your table looks like this:

--drop table CustomCatalogForm
create table CustomCatalogForm
 (
    CustomCatalogFormId  int  not null
   ,DisplaySeq char(1) not null
 )

insert CustomCatalogForm (CustomCatalogFormId, DisplaySeq)
 values (1,'A')
insert CustomCatalogForm (CustomCatalogFormId, DisplaySeq)
 values (2,'B')
insert CustomCatalogForm (CustomCatalogFormId, DisplaySeq)
 values (3,'C')
insert CustomCatalogForm (CustomCatalogFormId, DisplaySeq)
 values (4,'D')

Then this will do it for any two given values (I tested with 2 and 3):

select * from CustomCatalogForm
-------------------------------
DECLARE
  @Item1 int
 ,@Item2 int

SET @Item1 = 2
SET @Item2 = 3

UPDATE CustomCatalogForm
 set DisplaySeq = ccf2.DisplaySeq
 from CustomCatalogForm ccf
  inner join (select CustomCatalogFormId, DisplaySeq
               from CustomCatalogForm
               where CustomCatalogFormId = @Item1
                or CustomCatalogFormId = @Item2) ccf2
   on ccf.CustomCatalogFormId <> ccf2.CustomCatalogFormId
 where ccf.CustomCatalogFormId = @Item1
  or ccf.CustomCatalogFormId = @Item2
-------------------------------
select * from CustomCatalogForm

The trick is to get both rows only on either side of the join, and join on NOT EQUALS.

回答3:

Like this:

UPDATE CustomCatalogForm
SET DisplaySeq = (SELECT DisplaySeq
    FROM CustomCatalogForm T2
    WHERE T2.CustomCatalogFormId =
        -1*(CustomCatalogForm.CustomCatalogFormId -2) +1
    )
WHERE CustomCatalogFormId IN (1,2);

(warning: please test this first, as I cannot test it from here).

回答4:

Try This:

UPDATE ccf SET 
  DisplaySeq = Case CustomCatalogFormId 
     When 1 Then T2.DisplaySeq 
     When 2 Then T1.DisplaySeq  End
From CustomCatalogForm ccf 
    Join CustomCatalogForm T1 On T1.CustomCatalogFormId  = 1
    Join CustomCatalogForm T2 On T2.CustomCatalogFormId  = 2
Where ccf.CustomCatalogFormId  In (1,2)