How to Swap 2 Unique Primary Keys in the Same Table With MySQL

Sometimes, we need to swap two unique primary keys in MySQL database tables. We can use two separate update queries like below.

UPDATE table_name SET id = 1 WHERE id = 2;
UPDATE table_name SET id = 2 WHERE id = 1;

But it does not work as we expected. The reason is, it tries to create duplicate primary keys. The 1st query tries to change the id value of the 2nd record to 1. But that id = 1 record already exists. So MySQL rejects the execution of the query as there cannot be any duplicate primary key.

But we can slightly modify the above queries to avoid trying to create duplicate primary keys in 3 steps. First, we can change the id of the first row to a non-existing, temporary value something like 0. Then we can change the 2nd row id to 1. At this moment, it will not make any conflicts like before as the original first row id value is 0. Then, we can change the id of the original first row id to 2.

Before running this, make sure to replace the table_name, primary key ids 1 and 2 with your related primary key ids.

UPDATE table_name SET id = 0 WHERE id = 1;
UPDATE table_name SET id = 1 WHERE id = 2;
UPDATE table_name SET id = 2 WHERE id = 0;

Finally, we can see both 1st and 2nd row ids have successfully swapped.