Update field with rownumber in oracle

Question

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?

Answer 1

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