Delete rows out of table that is innerjoined and unioned with 2 others

We have 3 ta开发者_开发知识库bles (table1, table2, table3), and I need to delete all the rows from table1 that have the same ID in table2 OR table3. To see a list of all of these rows I have this code:

(
 select 
    table2.ID,
    table2.name_first,
    table2.name_last,
    table2.Collected
 from 
    table2
 inner join 
    table1
 on 
    table1.ID = table2.ID
 where
    table2.Collected = 'Y'
 )

 union

 (
 select
    table3.ID,
    table3.name_first,
    table3.name_last,
    table3.Collected
 from 
    table3
 inner join 
    table1
 on 
    table1.ID = table3.ID
 where
    table3.Collected = 'Y'
 )

I get back about 200 rows. How do I delete them from table1? I don't have a way to test if my query will work, so I'm nervous about modifying something I found online and potentially deleting data (we do have backups, but I'd rather not test out their integrity).

TIA!

EDIT You are correct, we are on MSSQL 2008. Thanks so much for all the replies, I will try it out tomorrow!

Try this:

DELETE FROM Table1 WHERE
ID IN
(
 SELECT ID FROM Table2 WHERE Collected = 'Y'
 UNION ALL
 SELECT ID FROM Table3 WHERE Collected = 'Y'
)

To test this query you can create dupicate tables using into clause i.e.(I assume it is SQL Server):

SELECT * INTO DUP_Table1 FROM Table1;
SELECT * INTO DUP_Table2 FROM Table2;
SELECT * INTO DUP_Table3 FROM Table3;

and then run this query:

DELETE FROM DUP_Table1 WHERE
ID IN
(
 SELECT ID FROM DUP_Table2 WHERE Collected = 'Y'
 UNION ALL
 SELECT ID FROM DUP_Table3 WHERE Collected = 'Y'
)

EDIT: Added the Collected Criteria and used UNION ALL (@Thomas: Thanks..) I think performance of UNION ALL is better than UNION when there is no need for uniqueness in the result.

DELETE FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table2.id = table1.id AND table2.collected = 'Y')
OR EXISTS (SELECT 1 FROM table3 WHERE table3.id = table1.id AND table3.collected = 'Y')

If you're feeling nervous about a big delete like this, put it into a transaction: that way you can at least check the row count before running commit (or rollback, of course :p)

Make sure foreign keys are setup properly and turn on cascade deletes. But if that's not an option, the correct SQL query is as follows:

begin tran

delete from table1
where exists(select * from table2 where table1.id = id and collected='Y')
or exists(select * from table3 where table1.id = id and collected='Y')

-- commit tran
-- rollback tran

if the SQL runs as expected, execute the "commit tran", otherwise execute the "rollback tran".