Hi Helen....
 
Thanks for your valuable efforts to explaining me.
 
Ya, i will try it and will soon get back to you.
 
Thanks once again.
 
Have a Nice Day.
 
 
Vishal Tiwari...

--- On Mon, 14/9/09, Helen Borrie <helebor@...> wrote:


From: Helen Borrie <helebor@...>
Subject: Re: [firebird-support] Speed Optimization required for DELETE query
To: firebird-support@yahoogroups.com
Date: Monday, 14 September, 2009, 7:23 PM


 



At 11:05 PM 14/09/2009, you wrote:
>
>Hi Helen.... Hi Set.....
>
>I came back again....
>
>Is there anything wrong with EXISTS predicate i used in Query no.2
>Because Query no. 1 deletes certain records where as Query no.2 deletes no records.

I haven't followed the thread in detail but I see that Query 2 has mutated in strange ways....

>
>Could you plz. tell me where i am making mistake for query no. 2.
>will ESISTS predicate give benifit in query no. 2, means can delete any records with some modification in query no. 2.

Can't quite work out what you are trying to say there...

But your Query 2 has nothing to correlate C with A and B.

Try this:

Delete from Table_C C
where
C.dt_date <= '03/01/2007'
And not exists (
select 1 from Table_A A

-- JOIN criteria
join Table_B B
On
A.var_EmpCode = B.var_EmpCode
And
A.var_PGCode = B.var_PGCode

-- search criteria
WHERE

A.var_EmpCode = C.var_EmpCode /* provides the correlation */
AND
(A.var_Status = 'I'
or
A.var_Resigned_ But_Not_Settled = 'Y')
and
Max(B.dt_AppliedDat e) <= '03/31/2007' );

./heLen

















Love Cricket? Check out live scores, photos, video highlights and more. Click here http://cricket.yahoo.com

[Non-text portions of this message have been removed]