That's not what I want, though. I want to UPDATE the actual values in the order_number column so they end up as 1, 2, 3 instead of 1, 3, 4.
So I want the result of a "select * from table order by order_number" to give me: a, 1 c, 2 d, 3 -Dave -----Original Message----- From: Dong Xuezhang-A19583 [mailto:[EMAIL PROTECTED] Sent: Sunday, May 08, 2005 8:24 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] SQL to renumber values in a column? Hi Dave If you query the database with order by, the result sequence is the order you are looking for, so you donot need to update at all. Like: select * from table order by order_number Row 1: a, 1 Row 2: c, 3 Row 3: d, 4 The result-set row number is the order you are looking for. Regards. Xuezhang. -----Original Message----- From: Brown, Dave [mailto:[EMAIL PROTECTED] Sent: Sunday, May 08, 2005 8:11 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQL to renumber values in a column? I have a table that has two columns: object_id, order_number The concept here is like a playlist of object IDs...the order number specifies what order to play or view the objects. The question I have is: If a row gets deleted, how do I sequentially reorder the remaining rows? For example: Start with this: a, 1 b, 2 c, 3 d, 4 now delete the row with 'b': a, 1 c, 3 d, 4 I'm trying to figure out how to write an update statement which will reorder this back to: a, 1 c, 2 d, 3 Help! -Dave
