It is possible to swap the values using a single updateable operation
-- Research -- How to swap values in a unique constraint scenario 20101103
USE tempdb
GO
IF OBJECT_ID('tempdb.dbo.#tmp') IS NOT NULL
BEGIN
PRINT 'Dropping table'
DROP TABLE #tmp
END
GO
CREATE TABLE #tmp
(
Id int PRIMARY KEY,
Column1 int NOT NULL,
Column2 int NOT NULL,
UNIQUE (Column1, Column2)
)
INSERT INTO #tmp
SELECT 1, 10, 2647 UNION ALL SELECT 2, 10, 3658
-- fails unless constraints are disabled, integrity risk
/*
BEGIN TRAN
UPDATE #tmp
SET Column2 = 3658
WHERE Id = 1
UPDATE #tmp
SET Column2 = 2647
WHERE Id = 2
COMMIT
*/
-- succeeds even if a unique constraint exists
-- no integrity risk, update both values at once...
UPDATE #tmp
SET Column2 =
CASE
WHEN Id = 1 THEN 3658
WHEN Id = 2 THEN 2647
ELSE Column2
END
WHERE
Id IN (1,2)
GO
