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

by

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

More Related Contents:

  1. The most efficient way to UPPER CASE two columns in a single query [closed]
  2. PL SQL UPDATE STATEMENT
  3. How ROWNUM works in pagination query?
  4. Oracle SQL: Update a table with data from another table
  5. How to use Oracle ORDER BY and ROWNUM correctly?
  6. SQL ROWNUM how to return rows between a specific range
  7. Selecting the second row of a table using rownum
  8. Oracle Update Query using Join
  9. Update with Join query in Oracle
  10. Update rows in one table with data from another table based on one column in each being equal
  11. Update a table with data from another table
  12. Current date in Oracle Db
  13. Merging two tables in SQL [closed]
  14. I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]
  15. Case insensitive searching in Oracle
  16. How to find the employee with the second highest salary?
  17. Oracle: What does `(+)` do in a WHERE clause?
  18. Concatenate results from a SQL query in Oracle
  19. Generate a range of dates using SQL
  20. PLS-00428: an INTO clause is expected in this SELECT statement
  21. How to kill a running SELECT statement
  22. Equivalent function for DATEADD() in Oracle
  23. Anonymous TABLE or VARRAY type in Oracle
  24. How do I convert a “legacy” left outer join statement in Oracle?
  25. How to Alter Constraint
  26. Group by with CLOB in select-statement
  27. How to handle optional parameters in SQL query?
  28. How to check if a column exists before adding it to an existing table in PL/SQL?
  29. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  30. Oracle ‘Partition By’ and ‘Row_Number’ keyword

Leave a Comment