| where exists elimination [message #375217] |
Mon, 06 August 2001 14:26  |
Jon Raven
Messages: 1
Registered: August 2001
|
Junior Member |
|
|
Can anyone help with a way to eliminate this "where exists" clause? Thanks, Jon
UPDATE L_B_IMPORT LBI SET LBI.R_RATE =
(SELECT distinct CRR.RATE
FROM CAR_REF CRRI
WHERE LBI.ID_KEY = CRRI.ID_KEY
AND CRRI.T_TYPE_KEY = 8)
WHERE EXISTS
(SELECT 1
FROM CAR_REF CRRI
WHERE LBI.ID_KEY = CRRI.ID_KEY
AND CRRI.T_TYPE_KEY = 8)
AND LBI.TERM_TYPE = ''9''
AND LBI.C_TYPE = ''3'';
|
|
|
|
| Re: where exists elimination [message #375250 is a reply to message #375217] |
Sun, 12 August 2001 14:28  |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
Try it out this way.
UPDATE L_B_IMPORT LBI SET LBI.R_RATE =
(SELECT distinct CRR.RATE
FROM CAR_REF CRRI
WHERE LBI.ID_KEY = CRRI.ID_KEY
AND CRRI.T_TYPE_KEY = 8)
WHERE
LBI.TERM_TYPE = '9'
AND LBI.C_TYPE = '3';
It is your same query but with little modification. I just removed the
EXISTS
(SELECT 1
FROM CAR_REF CRRI
WHERE LBI.ID_KEY = CRRI.ID_KEY
AND CRRI.T_TYPE_KEY = 8)
part right after the main WHERE verb, in your same query.
I just dont understand why you mentioned the condition
LBI.ID_KEY = CRRI.ID_KEY
AND CRRI.T_TYPE_KEY = 8
in EXISTS subquery as you are already checking it
in the first subquery to retrive the distinct (unique) RATE values of CAR_REF those which only satisfy the criteria. But why do you mention the same unnecessarily in the main EXISTS query? It is not necessary I guess. Try this out and compare the result to be sure. It should work.
Good luck.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
