CODEWITHSUNDEEP
mysqlsqlforeign-keyssql-delete
Brahim LAMJAGUAR
Brahim LAMJAGUAR

Reputation: 1354

Delete rows from a table with not in ( another table )

I have two tables A and B, in the B there is a foreign key from A, what I want to do is to delete all the rows from A that they don't have an occurrence in B, I execute the following query but it's not working :

DELETE from A 
WHERE  id_A
       not in (select DISTINCT(foreign_key_of_A_in_B) from B)

Any idea ?

Upvotes: 3

Views: 12473

Answers (2)

Powellellogram
Powellellogram

Reputation: 436

A standard for DELETE FROM table WHERE id NOT IN would look like this:

DELETE from Table_A 
WHERE  id -- ID of Table_A
       not in (select ID FROM Table_B)

This should find the IDs not in Table A from Table B, as your question states

Try this in a SELECT statement first to see if it returns the correct rows:

SELECT * from Table_A
WHERE  id -- ID of Table_A
       not in (select ID FROM Table_B)

Don't forget to cross-reference some rows to double check.

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1271171

My first recommendation is to try not exists rather than not in:

DELETE a FROM a
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.foreign_key_of_A_in_B = a.id_A);

NOT IN returns false or NULL if any value in the subquery is NULL. That is how the operator is defined. NOT EXISTS has more expected behavior. So, if you have any NULL values in the subquery, this will work (i.e. delete rows) but the NOT IN version will not.

I would recommend that you try the logic out using SELECT before doing a DELETE:

SELECT A.*
FROM A 
WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.foreign_key_of_A_in_B = A.id_A);

Upvotes: 14

Related Questions