Jump to content

updated records


somayajula_suresh
 Share

Recommended Posts

I have two tables. ODD ,ODDSSODD has cols: ITEM,LOC,ORDERS,QTYRCVODDSS has : ITEM,STATUS,LOCI/P: 1st table 2ND TABLEITEM LOC ORDERS QTYRCV ITEM STATUS LOC--------------------------------------- ---------------------------1001 201 0 230 1001 A 2011001 200 230 Null 1001 A 2002001 101 0 908 2001 A 1012001 100 908 null 2001 A 100..........After joing two tables i got the o/p as

                     (SELECT item,loc                             lag(qtyrcv)over (partition by item order by loc) new_qty                      FROM   ODD a,ODDS b                      WHERE  a.item=b.item                       AND    a.item in                     (select item from tab1 where order=0 and order<>0)                      and b.stat<>'C') v

ITEM LOC ORDERS QTYRCV STAT NEW_QTY------------------------------------------------------------1001 201 0 230 A 01001 200 230 NULL A 2302001 101 0 908 A 02001 100 900 NULL A 908after Joining the table I got around 100 records like above.Now I need to update thisBut Required O/P isITEM LOC ORDERS QTYRCV STAT -----------------------------------------------1001 201 0 0 A1001 200 230 230 A2001 101 0 0 A2001 100 900 908 A I wrote an Update stmt. But It'll update only one row I need to update total records.The code is as follows.

SQL> UPDATE ODD X1  2  SET    qtyrcv =  (select V.new_qty  3                   FROM ODD x ,  4                       (SELECT a.item,a.loc,a.qtyrcv,  5                        NVL(lead(a.qtyrcv)over (partition by a.item ordera.loc),0) new_qty  6                        FROM   ODD a,ODDS b  7                        WHERE  a.item=b.item  8                                       AND A.LOC=B.LOC  9                        AND    a.item in 10                       (select item from odd where orders=0 and qtyrcv  <>0 11                        and b.stat<>'C') v 12                    WHERE X.item = V.item 13                    AND   X.loc = V.loc 14                    AND   X.item =X1.item 15                    AND   X.loc = X1.loc) 16  WHERE  EXISTS (select NULL 17                   FROM ODD x , 18                       (SELECT a.item,a.loc,a.qtyrcv, 19                        NVL(lead(a.qtyrcv)over (partition by a.item ordera.loc),0) new_qty 20                        FROM   ODD a,ODDS b 21                        WHERE  a.item=b.item 22                                       AND A.LOC=B.LOC 23                        AND    a.item in 24                       (select item from odd where orders=0   and qtyrcv  <>0 25                        and b.stat<>'C') v 26                    WHERE X.item = V.item 27                    AND   X.loc = V.loc 28                    AND   X.item =X1.item 29                    AND   X.loc = X1.loc)

so can any one please check this code and give me clue for this.Thank you.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...