Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

how to update following data ???

Santosh.MinupureyMay 17 2010 — edited May 21 2010
i have 2 tables table A nd table B
i need to update content from table A to table B having following data


Table A Structure & Data (Table B structure is also same as B)
-----------------------------------
SQL> DESC A

EMPID NUMBER(3)
MONTH VARCHAR2(6)
GRADE VARCHAR2(1)
AMOUNT NUMBER

EMPID MONTH GRADE AMOUNT
-----------------------------------------------------------------
111 012007 A 100
111 042007 B 200
111 072007 C 300
222 062007 A 200
222 082007 B 400
222 112007 C 600


Table B contains complete data as (example for empid 111)

EMPID MONTH GRADE AMOUNT
-----------------------------------------------------------------
111 012007 x 50
111 022007 x 50
111 032007 x 50
111 042007 y 60
111 052007 x 50
111 062007 x 50
111 072007 x 50
111 082007 y 60
111 092007 z 65
222 062007 x 50
222 072007 x 50
222 082007 x 50
222 092007 y 60
222 102007 x 50
222 112007 x 50
222 122007 x 50
222 012008 y 60


output
----------
EMPID MONTH GRADE AMOUNT
-----------------------------------------------------------------
111 012007 A 100
111 022007 A 100
111 032007 A 100
111 042007 B 200
111 052007 B 200
111 062007 B 200
111 072007 C 300
111 082007 C 300
111 092007 C 300
222 062007 A 200
222 072007 A 200
222 082007 B 400
222 092007 B 400
222 102007 B 400
222 112007 C 600
222 122007 C 600
222 012008 C 600




so now i need to update Table B with Table A data

for example in Table A foe emp id has 3 records

so for 1st month in Table A (i.e 012007) i need to update grade and amount in table B w.r.t table A
this update should be done for all records until 2nd record in table A (i.e 012007,022007,032007)
i.e for months from 012007 to 032007 in table B the grade and amount to be update

2nd record in table A(empid 111) is 042007

so in table B from months 042007 to 062007 2nd record data to be updated



Regards,
Santosh Minupurey
This post has been answered by Aketi Jyuuzou on May 17 2010
Jump to Answer

Comments

Aketi Jyuuzou
Answer
maybe
merge into b
using (select b.RowID as Row_ID,a.GRADE,a.AMOUNT,
       Row_Number() over(partition by b.RowID
       order by a.MONTH desc) as rn
         from b Join a
           on b.empID=a.empID
          and b.MONTH >= a.MONTH) c
on (b.RowID=c.Row_ID and c.rn=1)
when matched then
update set b.GRADE=c.GRADE,
           b.AMOUNT=c.AMOUNT;
Marked as Answer by Santosh.Minupurey · Sep 27 2020
Santosh.Minupurey
thanks a lot

problem solved but i can't understand the logic behind it

can u plzzzzzzzzzzzz explain how does it work ???



Reagrds,
Santosh.Minupurey
Aketi Jyuuzou
Santosh.Minupurey wrote:
problem solved but i can't understand the logic behind it

can u plzzzzzzzzzzzz explain how does it work ???
I hope that gurus explains above merge statement logic.
Because my English is not good at.
I live in Japan.
Sudhakar_B
Santosh,
I am no guru, but I'll attempt to describe MERGE statement. 
-- The subquery "SUBQUERY used below" in the MERGE statement...
select b.RowID as Row_ID,a.GRADE,a.AMOUNT,
       Row_Number() over(partition by b.RowID
       order by a.MONTH desc) as rn
         from b Join a
           on b.empID=a.empID
          and b.MONTH >= a.MONTH
...builds a "result set" that contains ROWID from table B and grade and amount columns.
In addition it uses ROW_NUMBER (analytic function) to assign sequential number in descending order of month.
You can run this sub-query independently and see the result set.

As for MERGE statement... 
For each row in the "result set" from the sub-query (above) 
    find the match in table B using ROW_ID and picks up only *first row* from [result set]
        when match is found 
         then updates the correcsponding GRADE and AMOUNT fields in table B.
merge into b 
using (["SUBQUERY from above"]) c
on (b.RowID=c.Row_ID and c.rn=1)
when matched then
update set b.GRADE=c.GRADE,
           b.AMOUNT=c.AMOUNT;
FYI... Please read through the MERGE syntax and documentation
http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9016.htm#SQLRF01606

Hopefully, this gves a brief explanation of the construct.

@ Aketi,
Awsome use of row_number().

vr,
Sudhakar B.
1 person found this helpful
Aketi Jyuuzou
Oh thank you for your explanation :-)
Santosh.Minupurey
thanks a lot sudha for ur explaination


Regards
Santosh.Minupurey
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 18 2010
Added on May 17 2010
6 comments
1,532 views