All Forums Database
ashokn1989 1 post Joined 10/14
19 Aug 2016
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.!!
 

 
 
 
 

Akshay.Shaha 4 posts Joined 02/13
31 Aug 2016

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. 

You must sign in to leave a comment.