The accepted answer to sql swap primary key values fails with the error
Can't reopen table: 't'
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