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

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)

CodePudding user response：

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;