17

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?

Lukman
  • 18,462
  • 6
  • 56
  • 66
  • 2
    As soon as the statement completes, and another DML operation occurs, the `sequence_column` will be wrong/out of date. Why not put sequence_column (and the numbering) in a view - that will always be correct. – Damien_The_Unbeliever May 23 '11 at 07:24
  • @Damien_The_Unbeliever I'm not sure if I understand you, but there is no concern with future insertion of record into the table because the column is unique-indexed and the script that does the insertion is guaranteed put next running number into the column. It's just that at times when records are deleted the column is no longer sequential and needs to be re-sequenced. – Lukman May 23 '11 at 07:32
  • @Lukman, I agree with Damien. It's better to calculate `ROWNUM` or `ROW_NUMBER()` on the fly – Lukas Eder May 23 '11 at 07:34
  • @Lukman - but if you put this numbering in a view, you'd never have to do this maintenance activity at all. – Damien_The_Unbeliever May 23 '11 at 07:38
  • @Lukas I have a complex procedure with validation scripts and audit logs that depend heavily on the sequence numbering so I want the number to be hard-coded in the column so that it won't change throughout the procedure. My coordinator is very particular about having the data in the tables rather than dynamically generated on the fly due to audit reasons .. – Lukman May 23 '11 at 07:38
  • @Lukman, I see. That makes sense, somehow – Lukas Eder May 23 '11 at 07:45

4 Answers4

29

This should work (works for me)

update table_a outer 
set sequence_column = (
    select rnum from (

           -- evaluate row_number() for all rows ordered by your columns
           -- BEFORE updating those values into table_a
           select id, row_number() over (order by column1, column2) rnum  
           from table_a) inner 

    -- join on the primary key to be sure you'll only get one value
    -- for rnum
    where inner.id = outer.id);

OR you use the MERGE statement. Something like this.

merge into table_a u
using (
  select id, row_number() over (order by column1, column2) rnum 
  from table_a
) s
on (u.id = s.id)
when matched then update set u.sequence_column = s.rnum
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Oracle returns error: `ORA-30483: window functions are not allowed here` when I use it in the `SET` clause >__< .. – Lukman May 23 '11 at 07:23
  • See my update. I've tried it with a simpler table. It'll give you an idea for a solution for your situation – Lukas Eder May 23 '11 at 07:31
  • I executed the statement and it is still running right now, almost 5 minutes. The table only has about 15K records though so the slowness is strange .. – Lukman May 23 '11 at 07:40
  • @Lukman, no I'd say it's quite expected, if you consider the fact that the whole table needs to be sorted, row_number() evaluated for every single record... What you *could* try too, however is a `MERGE` statement – Lukas Eder May 23 '11 at 07:43
  • I'm totally fine with MERGE statement because it is my favorite SQL construct :) .. but I don't see how I can force the ordering in a MERGE statement. Care to enlighten? – Lukman May 23 '11 at 07:48
  • @Lukman, you're right. It doesn't work with `ROWNUM`. Use the `ROW_NUMBER()` window function instead. Besides: Yeah, the `MERGE` statement is hilarious! :) The "does-it-all" statement, so to speak – Lukas Eder May 23 '11 at 07:52
3

First Create a sequence :

CREATE SEQUENCE SEQ_SLNO
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

after that Update the table using the sequence:

UPDATE table_name
SET colun_name = SEQ_SLNO.NEXTVAL;
The Hungry Dictator
  • 3,444
  • 5
  • 37
  • 53
Palavesam
  • 39
  • 1
3
 UPDATE table_a
     SET sequence_column = (select rn 
                             from (
                                select rowid, 
                                      row_number() over (order by col1, col2)
                                from table_a
                            ) x
                            where x.rowid = table_a.rowid)

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.

1

A small correction just add AS RN :

UPDATE table_a
     SET sequence_column = (select rn 
                             from (
                                select rowid, 
                                      row_number() over (order by col1, col2) AS RN
                                from table_a
                            ) x
                            where x.rowid = table_a.rowid)
Gynteniuxas
  • 7,035
  • 18
  • 38
  • 54
Juan José López Aguilera
  • 11
  • 1