MySQL swap primary key values

Question

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?

fancyPants · Answer 1 · 2012-09-06T15:34:08.783

1

Don't use temporary tables for this.

From the manual:

You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:

mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.

UPDATE:

Sorry if I don't get it right, but why does a simple three way exchange not work?

Like this:

create table yourTable(id int auto_increment, b int, primary key(id));

insert into yourTable(b) values(1), (2);
select * from yourTable;

DELIMITER $$
create procedure pkswap(IN a int, IN b int)
BEGIN
select @max_id:=max(id) + 1 from yourTable;
update yourTableset id=@max_id where id = a;
update yourTableset id=a where id = b;
update yourTableset id=b where id = @max_id;
END $$
DELIMITER ;

call pkswap(1, 2);

select * from yourTable;

edited Sep 06 '12 at 15:34

answered Sep 06 '12 at 15:00

fancyPants

50,732
33
89
96

Who mentioned `TEMPORARY` tables? – eggyal Sep 06 '12 at 15:01
Well, using a non-temporary table, I get the error "duplicate entry for key". – Niet the Dark Absol Sep 06 '12 at 15:04
Well, that's a lot of work to go through when a three way exchange can be done much more easily... – Niet the Dark Absol Sep 06 '12 at 15:37
Please explain your problem a bit more. I don't get it. – fancyPants Sep 06 '12 at 15:42
What's a lot of work here? You create the procedure once and call it whenever you need. Plus, this solution is fool prove. – fancyPants Sep 06 '12 at 15:43
`select @max_id:=max(id) + 1 from yourTable;` isn't very concurrency-safe. What happens if a record with that `id` gets inserted before the following `update`? – eggyal Sep 06 '12 at 15:57
Thought that it would be concurrency-safe by putting it in a procedure? This is not the case? Thought a procedure would be treated as one transaction. – fancyPants Sep 06 '12 at 15:58
Alternatively and to keep things simple just update it to 0 instead of `max(id)+1` if it's impossible that there are values lower than 1. – fancyPants Sep 06 '12 at 16:15

score 0 · Accepted Answer · edited Apr 25 '16 at 11:20

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

@Kolink: I tested on an InnoDB table, and you are correct. The statement does work on a MyISAM table. I'd recommend the three statement approach, that should work on MyISAM, InnoDB and temporary (also MyISAM) tables. Actually, my testing with InnoDB reveals that 1 becomes 0, 2 becomes 1, and then 1 gets changed back to 2 again... testing with MyISAM yields a different result. — spencer7593, Sep 06 '12 at 16:00

MySQL swap primary key values

2 Answers2

Linked