Delete using 'Exists'

[cawthor]

Hi,

I am trying to delete records from one table where they exist in another table. I am using the following syntax:

DELETE T1.* FROM T1
WHERE EXISTS (
SELECT T1.* FROM T1, T2 WHERE
T1.ID = T2.ID AND T2.DELETE='YES')

If I just run the select query then it returns the 1 record I want to delete from T1. However, when I run the above query it returns all the rows from T1.

Any Ideas?

 

[PHV]

DELETE * FROM T1
WHERE ID IN (SELECT ID FROM T2 WHERE T2.DELETE='YES')


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

 

[cawthor]

oops...sorry, I was trying to simplify the query. I actually need to qualify on 2 values, not just one...so my query looks like this:

DELETE T1.* FROM T1
WHERE EXISTS (
SELECT T1.* FROM T1, T2 WHERE
T1.FIELD1 = T2.FIELD1 AND
T1.FIELD2 = T2.FIELD2 AND
T2.DELETE='YES')

 

[PHV]

DELETE T1.* FROM T1
WHERE EXISTS (
SELECT * FROM T2 WHERE
T2.FIELD1 = T1.FIELD1 AND
T2.FIELD2 = T1.FIELD2 AND
T2.DELETE='YES')

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

 

[cawthor]

THats great...THanks!!

I'm not sure HOW this works? I mean you are selecting everything from T2 in the select statement, how does the EXISTS match the T1 table with the T2 record? I thought I would need to select from the T1 table in the subselect in order for the record to ExIST?

 

[PHV]

This is a correlated subquery:
DELETE T1.* FROM [highlight]T1[/highlight]
WHERE EXISTS (
SELECT * FROM T2 WHERE
T2.FIELD1 = [highlight]T1.FIELD1[/highlight] AND
T2.FIELD2 = [highlight]T1.FIELD2[/highlight] AND
T2.DELETE='YES')

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