MySQL: Swap ID between two rows

Question

We have existing table:

---

| id |    name    |   color   | calories |
------------------------------------------
| 1  | apple      | red       | 20       |
| 2  | orange     | orange    | 10       |
| 3  | grapes     | green     | 5        |
| 4  | bananas    | yellow    | 15       |
| 5  | plum       | purple    | 25       |
------------------------------------------

Column id is unique and primary key. I'm facing a problem with edit/update records when, i heed to change for example in row 3 where, id = 3 to id = 5 and row 5 where, id = 5 to id = 3... Like swap id between rows 3 and 5. Of course there are duplicate, but how to resolve this scenario...

But MySQL logically throws out the error.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5' for key 'PRIMARY'

I know it is stupid scenario, but we have a customer who wants it that way. He want to edit/update each existing records.

Answer 1

These should be wrapped in a transaction using an intermediate value;

begin;
set @from = 3;
set @to = 5;
set @tmpid = (2000000 + @from % 147483647);
update col set id=@tmpid where id = @from;
update col set id=@from where id=@to;
update col set id=@to where id = @tmpid;
commit;

Choosing a high end @tmpid namespace to avoid collisions is important not to run into trouble later.

Answer 2

Column id is unique and primary key ... Like swap id between rows 3 and 5.

You should not do this.

The Primary Key of any record should be created/set/generated when the record is first created and it should never change, not for the entire lifetime of that record, right up to the point when the record is finally deleted.

Imagine the chaos if banks renumbered people's bank accounts!

Also, while you don't appear to have them here (yet), one of the main benefits of having a Primary Key is that other tables can have Foreign Keys that refer to it. If you start changing the Primary Key value, then you have to change all the corresponding Foreign Key values as well (the database will enforce this).
I seriously doubt any User will understand this.

... customer ... want to edit/update each existing records.

By all means allow them to edit the data, just not the Primary Key.
I'm going to assume that you're providing some sort of UI for this and that they're not issuing SQL directly against the database.

If they're trying to use it as some sort of sequence or "order", then using a separate column is the way to go. Primary Keys, even auto-increment ones, are guaranteed to be unique; they are not guaranteed to be sequential or contiguous.

Answer 3

I concur that it is alw2ays a bad idea to change the ids especially when they are used in foreign keys..

I also not very happy with the triangle swap.

So when you try to update old ids and are very sure that have made a backup and have thought about all consequences that will imply.

you can use a stored procedure, which will be saver(example borrowed from Charlieface)

create table t(id int UNIQUE,v varchar(10));

insert into t (id,v)
values (2,'c'),(3,'a'), (5,'b');

select * from t

id | v 
-: | :-
 2 | c 
 3 | a 
 5 | b 

CREATE PROCEDURE procedure_name(IN _a BIGint , IN _b BIGint)
BEGIN
   CREATE TEMPORARY TABLE T1 SELECT _b,v  FROM t WHERE id = _a;
   DELETE FROM t WHERE id = _a;
   UPDATE t SET id = _a WHERE id = _b;
   INSERT INTO t SELECT * FROM T1;
END 

CALL procedure_name(3,5)

select * from t

id | v 
-: | :-
 2 | c 
 3 | b 
 5 | a 

db<>fiddle here

Answer 4

You can do the update as a single statement, but this only works in SQL Server, not MySQL

update col
set id = case when id = 3 then 5 else 3 end
where id in (3, 5);

SQL Server db<>fiddle

MySQL db<>fiddle

---

In SQL Server, an update to the primary key (or any unique key) is evaluated at statement-level. It does this internally by using a Split->Sort->Collapse plan, which means that it effectively becomes a delete, an update, and an insert, and there is therefore no constraint violation.

See also these two articles by Paul White and Fabiano Amorim

---

A correct implementation of constraints would have the IMMEDIATE or DEFERRED options. Both of these require constraint checking at the statement level, not the row level.

MySQL supports neither, because it checks constraints on a row-by-row basis. PostgreSQL also checks IMMEDIATE constraints on a row level

But SQL Server only checks on a statement level. You can see this in the documentation.

Answer 5

As others have said, swapping IDs doesn't make any sense.

I think the best approach for this particular customer is to let them make edits (e.g. in a spreadsheet) and then truncate the table and re-insert the rows that the customer has specified.