Oracle: Updating a Table Column Using Rownum in Conjunction with Order by Clause

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

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

Using rownum in oracle SQL statement in combination with order by clause

Your Second Query will work

Because in the first ,the first ten rows with Status 0 are selected and then the order by is done in that case the first ten rows fetched need not be in the highest order

Update field with rownumber in oracle

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

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

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

UPDATE statement to generate row number

You can just do:

update MyTable set NewId = rownum;

SQL Fiddle.

But presumably you'll want to increment the NewId column for future inserts, quite likely with a sequence and maybe a trigger. You'd need to make the sequence start with the highest value you set manually (i.e. the number of rows in the table when you run the update), so you might as well just use the sequence here:

create sequence MyTableSeq;
update MyTable set NewId = MyTableSeq.nextval;

SQL Fiddle.

Both assume this is a purely synthetic key and you don't want to impose any ordering as it's generated.

Using rownum with the combination of between keyword

If I had to guess, I'd say that the reason you're not seeing what you expect (in addition to having the operators backwards, as pointed out by @diagonalbatman) is that you didn't tell the database what order you wanted the rows in. You're essentially telling the database to return any 5 rows. You can't even be sure that this query will always return the same five rows. Any time you're getting a subset like this, you should use an order by clause in the innermost query, so that the sort is applied before the rownum values are issued:

SELECT *
FROM   (SELECT ROWNUM rnum, a.*
        FROM   (SELECT   *
                FROM     emp
                ORDER BY emp_id) a
        WHERE  ROWNUM <= 8)
WHERE  rnum >= 4;

---

Related Topics