CODEWITHSUNDEEP
sql-server
BetterLateThanNever
BetterLateThanNever

Reputation: 906

Swap values in a column in SQL Server table

I am trying to update my tables data(1=>3, 2=>1, 3=>2) by swapping them using below queries.

/* Temporarily set 1 to a dummy unused value of 11 
   so they are disambiguated from those set to 1 in the next step */
update <tablename> 
set id = 11
where id = 1

update <tablename> 
set id = 1
where id = 2

update <tablename> 
set id = 2
where id = 3

update <tablename> 
set id = 3
where id = 11

Wondering if I can optimize my script.

Upvotes: 1

Views: 2035

Answers (1)

Martin Smith
Martin Smith

Reputation: 452957

You can just use case. Conceptually the operation happens "all at once" so there's no need to use a fourth dummy value as in your sequential approach.

UPDATE YourTable
SET ID = CASE ID WHEN 1 THEN 3
                 WHEN 2 THEN 1
                 WHEN 3 THEN 2
         END
WHERE ID IN (1,2,3)

Though changing ids is unusual as they should generally be immutable.

Upvotes: 6

Related Questions