Search code examples
sqliteauto-increment

Change row id without corrupting auto increment sqlite

I have a sqlite table with 10 rows. The structure is like this :

--------------------
id - Name - Address
1  - John - LA
2  - Mike - CA
      ...
---------------------

I want to change the last row id ( id = 10 ) to ( id = 2 ) and change the second ow ( id = 2) to be the last row ( id = 10) without corrupting Auto increment so When I Insert new row it doesn't break or start over.

How can I achieve that ?

Solution

  • Autoincrementing is all about the largest value used in the table, so you can simple use some small value that you know is not otherwise used:

    BEGIN;
UPDATE MyTable SET rowid = -12345 WHERE rowid = 10;
UPDATE MyTable SET rowid = 10     WHERE rowid = 2;
UPDATE MyTable SET rowid = 2      WHERE rowid = -12345;
COMMIT;