1

Possible Duplicate:
Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause

I have a query that returns an ordered set of records, and i want to copy the rownumber of each row in the result set into a particular field called Sequence on the record itself.

SELECT ROWNUM, ID
FROM (
    SELECT ID 
    FROM MYTABLE 
    WHERE PARENT = 1
    ORDER BY SEQUENCE ASC
)

I have the ID of each row, and the rownum I want to put in the SEQUENCE field. How can I loop through this result set and update the table accordingly?

Community
  • 1
  • 1
user1365247
  • 337
  • 7
  • 17
  • 4
    It's very similar to http://stackoverflow.com/questions/6094039/oracle-updating-a-table-column-using-rownum-in-conjunction-with-order-by-clause – igr Jan 31 '13 at 12:41
  • @user1365247, Always try using Row_Number() instead of Rownum. – Art Jan 31 '13 at 15:39

2 Answers2

3
merge into mytable t
using (SELECT ROWNUM rn, ID
       FROM (
         SELECT ID 
         FROM MYTABLE 
         WHERE PARENT = 1
         ORDER BY SEQUENCE ASC
      )) S 
on (t.id=s.id)
when matched then update set
  t.sequence=s.rn

If id is not unique, you can, for sure:

merge into mytable t
using (SELECT ROWNUM rn, rwd
       FROM (
         SELECT rowid rwd
         FROM MYTABLE 
         WHERE PARENT = 1
         ORDER BY SEQUENCE ASC
      )) S 
on (t.rowid=s.rwd)
when matched then update set
  t.sequence=s.rn
Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
1

Please try:

  merge into EMP_MASTER x
  using (
          SELECT ID, ROW_NUMBER() OVER(ORDER BY SEQUENCE) y
          FROM MYTABLE 
          WHERE PARENT = 1
         ) s on (s.ID=x.ID)
         when matched then update set
  x.sequence=s.y;

which works for unique IDs.

TechDo
  • 18,398
  • 3
  • 51
  • 64