My table student is as follows:
| Name | Id |
|---|---|
| Dave | 3414 |
| Bob | 2861 |
The ID is the primary key value that has a unique constraint. I'm trying to swap the two primary key values, so Bob's ID is 3414 and Dave's ID is 2861. What's the quickest way to do this?
UPDATE student SET Id=2861 WHERE Id=3414;
UPDATE student SET Id=3414 WHERE Id=2861;
These two statements won't work as it will create duplicate primary keys.
You can do it in three steps (Wrapped in a transaction so it's atomic as far as other connections to the database are concerned), by first changing one of the PKs to a value that's not already in the database. Negative numbers work well, assuming your PKs are normally values >= 0, which is true of automatically generated rowid/INTEGER PRIMARY KEY values in Sqlite.
BEGIN;
UPDATE student SET Id=-Id WHERE Id=2861;
UPDATE student SET Id=2861 WHERE Id=3414;
UPDATE student SET Id=3414 WHERE Id=-2861;
COMMIT;
Somebody asked something like this here
but it apparently does not work in some versions
