CODEWITHSUNDEEP
oracle-databaseoracle11g
Rajesh
Rajesh

Reputation: 1620

Update a column of table with ROW_NUMBER

I am new to Oracle I need to Update a Column of a Table with ROW_NUMBER() in oracle

i.e 

UPDATE tablefull
SET newcolumn=ROW_NUMBER() OVER (PARTITION BY columnid ORDER BY datecolumn)-1

Since the Window function is not allowed in Update I had tried with joining the table with subquery of same table and do the update

update a
set a.newcolumn= b.upnum
from tablefull a
INNER JOIN (SELECT columnid,ROW_NUMBER() OVER (PARTITION BY columnid ORDER BY datecolumn)-1 AS upnum 
FROM tablefull) b ON b.columnid=a.columnid

Since the join and update is also not possible in oracle the above also did not worked out

Please anyone help me in the way to update the newcolumn of the table with ROW_NUMBER() OVER (PARTITION BY columnid ORDER BY datecolumn)-1 in Oracle

Upvotes: 0

Views: 183

Answers (1)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Put this query with row_number as source table of merge statement:

merge into tablefull tgt
using (select rowid rwd, columnid, 
              row_number() over (partition by columnid order by datecolumn) - 1 rn
         from tablefull) src
on (src.rwd = tgt.rowid and tgt.columnid = src.columnid)
when matched then update set newcolumn = rn;

dbfiddle

Upvotes: 2

Related Questions