Niet the Dark Absol Niet the Dark Absol - 7 months ago 15
SQL Question

MySQL swap primary key values

The accepted answer to sql swap primary key values fails with the error

Can't reopen table: 't'
- presumably this has something to do with opening the same table for writing twice, causing a lock.

Is there any shortcut, or do I have to get both, set one of them to
NULL
, set the second one to the first one, then set the first one to the previously fetched value of the second?

Answer

To swap id values of 1 and 2, I would use a SQL statement like this:

EDIT : this does NOT work on an InnoDB table, only works on a MyISAM table, per my testing.

UPDATE mytable a 
  JOIN mytable b ON a.id = 1 AND b.id = 2 
  JOIN mytable c ON c.id = a.id
   SET a.id = 0
     , b.id = 1
     , c.id = 2 

For this statement to work, the id value of 0 must not exist in the table, any unused value would be suitable... but to get this to work in a single SQL statement, you need to (temporarily) use a third id value.


This solution works for regular MyISAM tables, not temporary tables. I missed that this was being performed on a temporary table, I was confused by the error message you reported Can't reopen table:.

To swap id values 1 and 2 in a temporary table, I'd run three separate statements, again, using a temporary placeholder value of 0:

UPDATE mytable a SET a.id = 0 WHERE a.id = 1;
UPDATE mytable b SET b.id = 1 WHERE b.id = 2;
UPDATE mytable c SET c.id = 2 WHERE c.id = 0;

Edit: Fixed errors