views:

195

answers:

4

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.

+2  A: 

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
Guffa
Clever trick; works as long as the DisplaySeq values added won't overflow the limits of the type.
Jonathan Leffler
Very interesting trick Guffa but i don't see that it is deterministic? E.g. how are you guaranteed that the values will actually swap in the Update call? Is there some stacking logic you're depending on?
Paul Sasik
@psasik: It's rather simple, actually. First you get (x+y), then you can use that to calculate one value from the other: x = (x+y)-y and y = (x+y)-x.
Guffa
Thanks Guffa, now i see it! It's so simple that i missed the first time around!
Paul Sasik
This does solve the problem and is definitely elegant, but it wouldn't work with non-numeric values.
Philip Kelley
A: 

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).

RBarryYoung
You haven't defined T1 anywhere, and you don't use T2 anywhere...
Guffa
Yup, hnxs. Should have been T2 both places.
RBarryYoung
A: 

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.

Philip Kelley
A: 

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)
Charles Bretana