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_AppliedDate) <= '03/31/2007' );

./heLen