Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE QUERY (ORACLE 10.2.0.2)
UPDATE QUERY [message #351573] Wed, 01 October 2008 10:56 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Here is my tabel. COL1,COL2,COL3,MTH are the primary keys.

COL1  COL2  COL3  MTH       PRICE      PRICE_EFF_IRM

I1    T1    C1    200803    10          NULL
I1    T1    C1    200804    10          NULL 
I1    T1    C1    200805    10          NULL
I1    T1    C1    200806    10.5        NULL
I1    T1    C1    200807    10.5        NULL 


I need to update the price_eff_irm like below.

COL1  COL2  COL3  MTH       PRICE      PRICE_EFF_IRM

I1    T1    C1    200803    10          200803
I1    T1    C1    200804    10          200803
I1    T1    C1    200805    10          200803
I1    T1    C1    200806    10.5        200806
I1    T1    C1    200807    10.5        200806



i wrote the qeury like below. Logic is the find the effective month of the price change.



UPDATE PRICE A
SET PRICE_EFF_IRM = (SELECT MIN(mth) FROM PRICE B
                     WHERE A.col1 = B.col1 AND
                           A.col2 = b.col2 and
                           A.col3 = b.col3 and
                           A.mth = b.mth  
                    GROUP BY col1,col2,col3,mth,price)
WHERE col1 in  ( SELECT distinct col1 from price);                            


The above update takes long time. is there a better way to write??

my price table contains 6 million records.

Anu
Re: UPDATE QUERY [message #351581 is a reply to message #351573] Wed, 01 October 2008 11:26 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Read this link and come back to us with the required information.

http://www.orafaq.com/forum/t/84315/94420/

Regards

Raj
Previous Topic: Rowtype Declaration Problem Within Multiple Schemas (merged)
Next Topic: timestamp
Goto Forum:
  


Current Time: Wed Dec 07 10:48:08 CST 2016

Total time taken to generate the page: 0.11493 seconds