Geek Answers Handbook

How to delete all records in a table that have corresponding records in another table

I have two tables A and B. I would like to delete all 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 tried:

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

but this only works if the internal selection actually returns a value (not if the result set is empty)

NB: this should work on and SQLServer AND MySQL

EDIT: additional information

The above deletion works 100% on SQLServer

When starting up in MySQL, I get a "SQL syntax error" message indicating the beginning of SELECT as a problem. if I substitute the inner choice with (1,2), then it works.

@Kibbee. You are right, it doesn’t really matter if the internal selection returns rows or not.

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

+4
source share
8 answers

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

No nicknames:

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

I am not sure why your method fails. If the internal query returns an empty set, then the first request should also return an empty set. I don't think @Fred's solution is correct, as it seems to be joining the wrong column.

+3
source

Or you can switch to exist syntax with 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 will be returned by the subquery), this can be faster, since there is no need to completely generate the full set of results ... It can stop as soon as it finds one record. ..

+2
source

You were not so far from an answer!

Message edited: delete alias in tables A and B

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

You can also use ON CASCADE in your child table so that when you delete a row in the parent table, it automatically deletes the child rows in the child table. This way you don't have to worry about referential integrity when deleting the parent line.

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

Another option in MYSQL and MSSQL, but its a long way:

 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
0
source

According to your description for your DELETE statement, do you want to delete empty orphs in table A?

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

(note the inverted connection method in B)

Must do the trick in this case. I'm not sure how MSSQL deals with delete connections, but I think it should work the same.

-1
source

More articles: