| query [message #2966] |
Mon, 26 August 2002 20:34  |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
hi,
thanks toddbarry for reply for my previous message.i want the following query to be optimised while using nvl function.INDEX is created for p_prodcode column.
UPDATE tab A
SET A.PRODDESC =
nvl((SELECT MAX(B.PRODDESC)
FROM tab B
WHERE
B.P_PRODCODE = A.P_PRODCODE),A.P_PRODCODE)
WHERE A.PRODDESC IS NULL
and A.P_BRG_FLAG IS NULL AND A.P_LOC_FLAG IS NULL;
|
|
|
|
| Re: query [message #2968 is a reply to message #2966] |
Mon, 26 August 2002 21:59  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Please post followup questions in the original thread.
You didn't mention anything about using NVL in your previous message. Even so, the query is optimized as you have written it if your intent is to update every row that meets the WHERE condition (the three specified columns are NULL) whether a matching description is found or not. This approach is fine if you expect to mostly find matches.
If not, then you can:
UPDATE tab A
SET PRODDESC = (SELECT MAX(B.PRODDESC)
FROM tab B
WHERE B.P_PRODCODE = A.P_PRODCODE)
WHERE A.PRODDESC IS NULL
and A.P_BRG_FLAG IS NULL
AND A.P_LOC_FLAG IS NULL
and exists (select null
from tab b
where b.p_prodcode = a.p_prodcode
and b.proddesc is not null);
I'm assuming you are using MAX here because there may be multiple matching rows in b for a given p_prodcode. If that column is NOT NULL in table b, you can remove the 'is not null' condition.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
