I want to populate a table column with a running integer number, so I'm thinking of using ROWNUM. However, I need to populate it based on the order of other columns, something like ORDER BY column1, column2
. That is, unfortunately, not possible since Oracle does not accept the following statement:
UPDATE table_a SET sequence_column = rownum ORDER BY column1, column2;
Nor the following statement (an attempt to use WITH clause):
WITH tmp AS (SELECT * FROM table_a ORDER BY column1, column2)
UPDATE tmp SET sequence_column = rownum;
So how do I do it using an SQL statement and without resorting to cursor iteration method in PL/SQL?
But that won't be very fast and as Damien pointed out, you have to re-run this statement each time you change data in that table.
First Create a sequence :
after that Update the table using the sequence:
A small correction just add AS RN :
This should work (works for me)
OR you use the
MERGE
statement. Something like this.