Search code examples
pythonsqlitesql-updaterow-number

Sqlite create/insert number sequence into existing column based on current number order

I'm using Python with SQLite and would like to insert a number sequence (1, 2, 3, 4...) into a single SELECTION_POSITION column in the SELECTION_GROUPS table. I tried to use row_number but am unsure how/if I can do an update with that rather than just a select:

cursor.execute('select SELECTION_POSITION, row_number() OVER (ORDER BY SELECTION_POSITION) AS row_number FROM SELECTION_GROUPS ORDER BY SELECTION_POSITION;')

If this cannot be done, is there another/better way to overwrite/insert values into this column based on that columns current order (trying to have it autocorrect itself if the program has found any duplicate/out of order entries and to keep it in as similar position order as possible)

Solution

  • If your version of SQLite is 3.33.0+ you can use the UPDATE...FROM syntax to update the table:

    UPDATE SELECTION_GROUPS AS t1
SET SELECTION_POSITION = t2.rn
FROM (
  SELECT rowid, 
         ROW_NUMBER() OVER (ORDER BY SELECTION_POSITION, rowid) AS rn
  FROM SELECTION_GROUPS
) AS t2
WHERE t2.rowid = t1.rowid;