Updating second row value to new column first row

Database
Enthusiast

Updating second row value to new column first row

Hi

I have a table

Id        rn            col1          col2         new_col

aa         1            592           650  

aa         2             750           800    

bb         1              250          600  

bb         2            300            null

output is

Id        rn            col1          col2         new_col

aa         1            592           650          650

aa         2             750           800         null

bb         1              250          600         null

bb         2            300            null          null

Logic is

If Id have a value in its col2 where rn=2, need to fetch the value of col2 where rn=1 to the new_col 

If Id don't have value in its col2 where rn=2 or null need to update as null to the new_col.

thanks in advance.!!

1 REPLY
Enthusiast

Re: Updating second row value to new column first row

Hi,

Is RN column part of your table? If yes, then below query should do :

MERGE INTO tab A USING

(

    SELECT id , col2 FROM tab WHERE rn = 2

) B (id,col2)

ON (A.id = B.id AND A.rn = 1)

WHEN MATCHED THEN UPDATE SET new_col=B.col2 ; 

PS - MERGE will not work on NoPI table.