Swap two primary keys in a SQL Server table

[SoftwareEng]

Guys,

I need to swap a value in two rows, which is used as a primary key. If you know DB design, you can't have duplicate primary keys, so I'm having to use some temp variables. Is there a way to avoid that and swap in-place or with fewer operations?

(Example: row A has a primary key ItemOrder=1, and row B has a primary key ItemOrder=4. I need to swapm the 1 and 4).

 

[Crusty]

It might be easier to disable foreign key checks, swap the values and then re-enable them.

 

[KLin]

BEGIN TRANSACTION
DECLARE @ItemOrder1 int
DECLARE @ItemOrder2 int
SET @ItemOrder1 = (SELECT ItemOrder from OrderTable where ItemOrder = 1)
SET @ItemOrder2 = (SELECT ItemOrder from OrderTable where ItemOrder = 4)
UPDATE OrderTable SET ItemOrder = 9999 WHERE ItemOrder = @ItemOrder1
UPDATE OrderTable SET ItemOrder = @ItemOrder1 WHERE ItemOrder = @ItemOrder2
UPDATE OrderTable SET ItemOrder = @ItemOrder2 WHERE ItemOrder = 9999
COMMIT TRANSACTION

Still needs variables.

 

[Evadman]

Update OrderTable set ItemOrder = CASE WHEN ItemOrder = 1 THEN 4 WHEN ItemOrder = 4 THEN 1 END

 

[SoftwareEng]

Originally posted by: KLin
BEGIN TRANSACTION
DECLARE @ItemOrder1 int
DECLARE @ItemOrder2 int
SET @ItemOrder1 = (SELECT ItemOrder from OrderTable where ItemOrder = 1)
SET @ItemOrder2 = (SELECT ItemOrder from OrderTable where ItemOrder = 4)
UPDATE OrderTable SET ItemOrder = 9999 WHERE ItemOrder = @ItemOrder1
UPDATE OrderTable SET ItemOrder = @ItemOrder1 WHERE ItemOrder = @ItemOrder2
UPDATE OrderTable SET ItemOrder = @ItemOrder2 WHERE ItemOrder = 9999
COMMIT TRANSACTION

Still needs variables.

thanks, this is also exactly what I had 🙂 Only I used -1 instead of the temp key of 9999, to be able to debug it later.

 

[SoftwareEng]

Originally posted by: Evadman
Update OrderTable set ItemOrder = CASE WHEN ItemOrder = 1 THEN 4 WHEN ItemOrder = 4 THEN 1 END

This looks shady, does this work? Gotta test it, looks too simple to be true 😱

 

[Evadman]

Originally posted by: SoftwareEng
This looks shady, does this work? Gotta test it, looks too simple to be true 😱

You question my work?! Well, actually I forgot the where clause, since you only want to update rows that are 1 or 4.

Here's a script that proves it works.

CREATE TABLE [dbo].[ordertable] (
[itemorder] [int] NOT NULL ,
[randomfield] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ordertable] WITH NOCHECK ADD
CONSTRAINT [PK_ordertable] PRIMARY KEY CLUSTERED
(
[itemorder]
) ON [PRIMARY]
GO


insert into ordertable(itemorder,randomfield)
values (1,'The key to this field started as 1')
insert into ordertable(itemorder,randomfield)
values (2,'The key to this field started as 2')
insert into ordertable(itemorder,randomfield)
values (3,'The key to this field started as 3')
insert into ordertable(itemorder,randomfield)
values (4,'The key to this field started as 4')

select * from ordertable


Update OrderTable set ItemOrder = CASE WHEN ItemOrder = 1 THEN 4 WHEN ItemOrder = 4 THEN 1 END WHERE ItemOrder in(1,4)

select * from ordertable

results:


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)


(1 row(s) affected)

itemorder randomfield
----------- --------------------------------------------------
1 The key to this field started as 1
2 The key to this field started as 2
3 The key to this field started as 3
4 The key to this field started as 4

(4 row(s) affected)


(2 row(s) affected)

itemorder randomfield
----------- --------------------------------------------------
1 The key to this field started as 4
2 The key to this field started as 2
3 The key to this field started as 3
4 The key to this field started as 1

(4 row(s) affected)