sql swap primary key values

by

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

More Related Contents:

  1. Auto Increment after delete in MySQL
  2. Strings as Primary Keys in SQL Database [closed]
  3. Foreign key referring to primary keys across multiple tables?
  4. How can I define a composite primary key in SQL?
  5. SQL – many-to-many table primary key
  6. Is there AUTO INCREMENT in SQLite?
  7. Improving performance of cluster index GUID primary key
  8. Does ‘Select’ always order by primary key?
  9. SQL – Inserting a row and returning primary key
  10. adding primary key to sql view
  11. Retrieve Oracle last inserted IDENTITY
  12. Store and reuse value returned by INSERT … RETURNING
  13. Select first row in each GROUP BY group?
  14. Is there any rule of thumb to construct SQL query from a human-readable description?
  15. Optimal way to concatenate/aggregate strings
  16. Doing a WHERE .. IN subquery in Doctrine 2
  17. SQL left join vs multiple tables on FROM line?
  18. Delete duplicate rows (don’t delete all duplicate)
  19. Return a value if no record is found
  20. Remove Primary Key in MySQL
  21. How can I select from list of values in Oracle
  22. Perform this hours of operation query in PostgreSQL
  23. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  24. Is there a difference between Select * and Select [list each col] [duplicate]
  25. Why are logical reads for windowed aggregate functions so high?
  26. Select corresponding to row from the same table SQL Server
  27. Is there any query for Cassandra as same as SQL:LIKE Condition?
  28. is there a PRODUCT function like there is a SUM function in Oracle SQL?
  29. Connect different Windows User in SQL Server Management Studio (2005 or later)
  30. In SQL how do I get the maximum value for an integer?

Leave a Comment