Forum Stats

  • 3,733,254 Users
  • 2,246,738 Discussions
  • 7,856,636 Comments

Discussions

how to update following data ???

Santosh.Minupurey
Santosh.Minupurey Member Posts: 118
edited May 2010 in SQL & PL/SQL
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

Best Answer

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited May 2010 Accepted 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;

Answers

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited May 2010 Accepted 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;
  • Santosh.Minupurey
    Santosh.Minupurey Member Posts: 118
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
    Sudhakar_B Member Posts: 501
    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.
    Sudhakar_B
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Oh thank you for your explanation :-)
  • Santosh.Minupurey
    Santosh.Minupurey Member Posts: 118
    thanks a lot sudha for ur explaination


    Regards
    Santosh.Minupurey
This discussion has been closed.