Niet the Dark Absol Niet the Dark Absol - 2 years ago 173
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
, set the second one to the first one, then set the first one to the previously fetched value of the second?

Answer Source

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 = 1 AND = 2 
  JOIN mytable c ON =
   SET = 0
     , = 1
     , = 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 = 0 WHERE = 1;
UPDATE mytable b SET = 1 WHERE = 2;
UPDATE mytable c SET = 2 WHERE = 0;

Edit: Fixed errors

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download