sql swap primary key values

Question

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

Mongo

I also have no idea what, in detail, you are trying to accomplish. – Fizzy

Why would you want to do that? Don't you like your primary keys? ;) – Antiparallel

And yes, it's possible. For example, in perl there is the fetchall_hashref that accepts any column name to be used. – Benoni

Answer 1

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

Adamantine

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'. – Palsgrave

Fails in MySQL 5.1.62 with ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'. – Bronwyn

Oracle also does seems not to allow two tables in one one update statement. techonthenet.com/sql/update.php: "The syntax for the SQL UPDATE statement when updating multiple tables (not permitted in Oracle) is:" – Whaleback

what would you do for php 5.5 and up? – Bicentenary

Error Code: 1706. Primary key/partition key update is not allowed since the table is updated both as 'core_website' and 'core_website2'. – Hanky

Answer 2

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)

Jurisprudence

Putting the raw key values as literals in the query solves the problem of duplicate keys during the transaction. – Oakes

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. – Thymus

Answer 3

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

Whaleback

Answer 4

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;

Gyneco