Need help with SQL DELETE statement 1

[addy]

OK, I have two tables and I need to delete some information from one table depending on information in another table.

To make it easy, let's call the two tables T1 and T2.

T1 has fields F1, F2, F3

T2 has fields F4, F5, F6 & F7

I need to delete all records in T2 where F4=F1, F5=F2 and F3 (in T1) = 'D'.

I have almost got it working using a DELETE Statement with a WHERE EXISTS criteria but it is trying to delete ALL records in T2, not just thise where F3 = 'D'.

If I 'run' my WHERE Exists criteria as a SELECT statement it pulls back the correct records but when I add it to my DELETE statement it wants to delete everything.

Any ideas?

Many Thanks.

 

[hwkranger]

DELETE FROM TABLE
<STATEMENT>

Randall Vollen
National City Bank Corp.

 

[PHV]

Have you tried this ?
DELETE T2.* FROM T2 INNER JOIN T1 ON T2.F4 = T1.F1 AND T2.F5 = T1.F2
WHERE T1.F3 = 'D'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[addy]

Thanks PHV - your code is very similar to what I had, except yours works and mine didn't!!

Cheers.