19

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

Thomas
  • 2,338
  • 2
  • 23
  • 32

4 Answers4

12

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).

Community
  • 1
  • 1
Unreason
  • 12,556
  • 2
  • 34
  • 50
  • 1
    Fails in MySQL 5.1.62 with `ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'`. – dotancohen Aug 08 '12 at 15:31
  • 6
    Your solution don't work in MySQL 5.5.22-log: `1706 - Primary key/partition key update is not allowed since the table is updated both as 'lae_marketing_invoice_history' and 't2'.` – Jose Nobile Aug 08 '12 at 15:19
  • Oracle also does seems not to allow two tables in one one `update` statement. http://www.techonthenet.com/sql/update.php: "The syntax for the SQL UPDATE statement when updating multiple tables (not permitted in Oracle) is:" – towi Nov 18 '15 at 09:03
  • what would you do for php 5.5 and up? – jPO Nov 14 '18 at 09:06
  • Error Code: 1706. Primary key/partition key update is not allowed since the table is updated both as 'core_website' and 'core_website2'. – Hassan Ali Shahzad Oct 23 '19 at 07:12
8

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)
David Harkness
  • 35,992
  • 10
  • 112
  • 134
Bart Juriewicz
  • 579
  • 7
  • 19
  • Putting the raw key values as literals in the query solves the problem of duplicate keys during the transaction. – David Harkness May 05 '17 at 22:43
  • Since you only have one table in the query it is not neccessary to alias it and put it before each column name. Also this syntax doesn't work with SQL Server. – Marcell Jun 14 '19 at 07:07
2

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);
towi
  • 21,587
  • 28
  • 106
  • 187
0

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;
Maxim Colesnic
  • 408
  • 1
  • 4
  • 12