updated records

[somayajula_suresh]

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.