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
] 
Blog
Search
Help
Members
Register
Login
Home
