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

You were not so far from the answer!

Post Edited: Remove alias on table A and B

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

Answer 2

I'm not sure why your method is failing. If the inner query returns an empty set, then the first query should also return an empty set. I don't think @Fred's solution is right, as he seems to be joining on the wrong column.

Answer 3

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';

Answer 4

According to your description to your DELETE-statement, you want to delete empty orphants in table A aswell?

DELETE A.*
FROM A 
LEFT JOIN B ON A.id = B.a_id AND b.date > '2008-10-10'
WHERE b.id IS NULL

(please note the inverted way of joining in B)

Should do the trick in that case. I'm not sure how MSSQL deals with join-deletes, but I guess that it should work the same way.

Answer 5

You could also use ON CASCADE in your child table so that when a row is deleted in your parent table it automatically deletes child rows in the child table. In that way you need not worry about referential integrity when a parent row is deleted.

Answer 6

delete from a inner join b on a.id = b.a_id and b.date < '2008-10-10'

Answer 7

Or you could switch to an exists syntax with a correlated subquery ...

Delete A 
From A
Where Exists 
      (Select * From B 
       Where B.Id = A.Id
         And B.date < '2008-10-10');

Depending on how smart the query optimizer is, (and how many records in Table B would be returned by the subquery) this could be faster, as an exists doesn't need to completely generate the full resultset... It can stop as soon as it finds one record...

Answer 8

Another option in MYSQL and MSSQL, but its long way round of doing it:

select b.ID
into #T
from 
    [Table b] with (nolock) 
where 
  b.date > '2008-10-10'

if exists (select * from #T with (nolock))
    delete from [Table a] where a.id in (select id from #T with (nolock))
    drop table #T