MySQL: Swap values between two rows with unique contraint

swap_unique_data_from_rows.md

# drop table tasks;

#  ddl
create table tasks
(
    id  int primary key,
    seq int not null unique
);

# seed
insert into tasks(id, seq)
values (1, 1),
       (2, 2),
       (3, 3);
       
select * from tasks;
# 1,1
# 2,2
# 3,3

START TRANSACTION;

update tasks set seq=@temp := -1 where id = 3;
update tasks set seq=3 where id = 2;
update tasks set seq=2 where id = 3;

COMMIT;

select * from tasks order by id;

# 1,1
# 2,3 * swapped
# 3,2 * swapped