CODEWITHSUNDEEP
sqlprimary-keyswap
Thomas
Thomas

Reputation: 2367

sql swap primary key values

is it possible to swap primary key values between two datasets? If so, how would one do that?

Upvotes: 19

Views: 17298

Answers (4)

Maxim Colesnic
Maxim Colesnic

Reputation: 465

If you have FOREIGN_KEYS and want to preserve AUTO_INCREMENT

BEGIN;
SET FOREIGN_KEY_CHECKS=0;
SET @from = 2;
SET @to = 3;
SET @tmpid = (2000000 + @from % 147483647);
SET @ai = (SELECT `AUTO_INCREMENT` FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name');
UPDATE table_name SET id=@tmpid WHERE id = @from;
UPDATE table_name SET id=@from WHERE id=@to;
UPDATE table_name SET id=@to WHERE id = @tmpid;
SET FOREIGN_KEY_CHECKS=1;
SET @sql = CONCAT('ALTER TABLE `table_name` AUTO_INCREMENT = ', @ai);
PREPARE st FROM @sql;
EXECUTE st;
COMMIT;

Upvotes: 0

Bart Juriewicz
Bart Juriewicz

Reputation: 629

I prefer the following approach (Justin Cave wrote similar somewhere):

update MY_TABLE t1
set t1.MY_KEY = (case when t1.MY_KEY = 100 then 101 else 100 end)
where t1.MYKEY in (100, 101)

Upvotes: 8

towi
towi

Reputation: 22267

Similar to @Bart's solution, but I used a slightly different way:

update t
set t.id=(select decode(t.id, 100, 101, 101, 100) from dual)
where t.id in (100, 101);

This is quite the same, but I know decode better then case.

Also, to make @Bart's solution work for me I had to add a when:

update t
set t.id = (case when t.id = 100 then 101 else 101 end)
where t.id in (100, 101);

Upvotes: 2

Unreason
Unreason

Reputation: 12704

Let's for the sake of simplicity assume you have two records

id   name
---------
1    john

id   name
---------
2    jim

both from table t (but they can come from different tables)

You could do

UPDATE t, t as t2
SET t.id = t2.id, t2.id = t.id
WHERE t.id = 1 AND t2.id = 2

Note: Updating primary keys has other side effects and maybe the preferred approach would be to leave the primary keys as they are and swap the values of all the other columns.

Caveat: The reason why the t.id = t2.id, t2.id = t.id works is because in SQL the update happens on a transaction level. The t.id is not variable and = is not assignment. You could interpret it as "set t.id to the value t2.id had before the effect of the query, set t2.id to the value t.id had before the effect of the query". However, some databases might not do proper isolation, see this question for example (however, running above query, which is probably considered multi table update, behaved according to the standard in mysql).

Upvotes: 12

Related Questions