CODEWITHSUNDEEP
sqlmysqlsql-server
Ron Tuffin
Ron Tuffin

Reputation: 54619

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

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

Upvotes: 4

Views: 1336

Answers (8)

EJ.
EJ.

Reputation: 21

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

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146499

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...

Upvotes: 2

Shawn
Shawn

Reputation: 19793

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

Upvotes: 0

lmop
lmop

Reputation: 941

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

Upvotes: 5

anonym0use
anonym0use

Reputation: 3046

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.

Upvotes: 1

Fred
Fred

Reputation: 5006

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

Upvotes: 1

jishi
jishi

Reputation: 24614

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.

Upvotes: -1

Kibbee
Kibbee

Reputation: 66122

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.

Upvotes: 3

Related Questions