How do I delete all the records in a table that have corresponding records in another table

Question

I have two tables A and B. I would like to delete all the records from table A that are returned in the following query:

SELECT A.*
FROM A , B
WHERE A.id = B.a_id AND
    b.date < '2008-10-10'

I have tried:

DELETE A 
WHERE id in (
    SELECT a_id 
    FROM B 
    WHERE date < '2008-10-10')

but that only works if the inner select actually returns a value (not if the result set is empty)

NB: this has to work on both SQLServer AND MySQL

EDIT: More information

The above delete works 100% on SQLServer

When running it on MySQL I get an "error in you SQL syntax" message which points to the start of the SELECT as the problem. if I substitute the inner select with (1,2) then it works.

@Kibbee You are right it actually makes no difference if the inner select returns rows or not.

@Fred I get a "not unique table.alias: a" message

Answer 1

I think this should work (works on MySQL anyway):

DELETE a.* FROM A a JOIN B b ON b.id = a.id WHERE b.date < '2008-10-10';

Without aliases:

DELETE A.* FROM A JOIN B ON B.id = A.id WHERE B.date < '2008-10-10';