SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT 1 AS ID, 1 AS [rank], 'Joe' AS name INTO t
UNION SELECT 2,2,'Ann'
UNION SELECT 3,5,'Mark'
UNION SELECT 4,7,'Sam';

CREATE UNIQUE INDEX uq ON t([rank]);
4 rows affected

SELECT * FROM t


ID
rank
name


1
1
Joe

2
2
Ann

3
5
Mark

4
7
Sam


UPDATE t SET [rank] = 5 where Name = 'Joe'
UPDATE t SET [rank] = 1 where Name = 'Mark'
Msg 2601 Level 14 State 1 Line 1
Cannot insert duplicate key row in object 'dbo.t' with unique index 'uq'. The duplicate key value is (5).
Msg 2601 Level 14 State 1 Line 2
Cannot insert duplicate key row in object 'dbo.t' with unique index 'uq'. The duplicate key value is (1).
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.

BEGIN TRAN
-- step 1

UPDATE t SET [rank] = -[rank] where Name = 'Joe';
UPDATE t SET [rank] = -[rank] where Name = 'Mark';


-- step 2
UPDATE t SET [rank] = 5 where Name = 'Joe'
UPDATE t SET [rank] = 1 where Name = 'Mark';
COMMIT;
4 rows affected

SELECT *
FROM t


ID
rank
name


1
5
Joe

2
2
Ann

3
1
Mark

4
7
Sam