CODEWITHSUNDEEP
sqlsql-servert-sql
Jake12342134
Jake12342134

Reputation: 1767

How do I delete all rows in a table which have a null foreign key reference?

I have two tables, A and B. A has a column B_Id which references the Id column of table B.

There are a number of rows in table A that have a value for B_Id, but that value does not exist anywhere in the list of Ids of table B.

I want to delete all of these particular rows.

I've tried

select * from A left join B on A.B_Id = B.Id where B.Id = null

But this doesn't select anything at all. How can I change the select to retrieve the rows I want?

Upvotes: 0

Views: 995

Answers (4)

Lajos Arpad
Lajos Arpad

Reputation: 76551

You need to use the IS operator, like

select * from A left join B on A.B_Id = B.Id where B.Id is null

the reason is that null is unkown and the language isn't certain whether something is equal to it or not. You can also do it via not exists as Gordon Linoff has already pointed out.

Whatever you try, make sure that you make a full backup of your table.

If B_Id is already a foreign key and foreign key checking is enabled for your database, then you can safely assume that every record that has no pair in B has a B_Id of null in A, which renders the join and checking for existence unnecessary.

Upvotes: 1

Luke
Luke

Reputation: 127

You can select the data you want to delete with

SELECT * FROM A
WHERE B_ID NOT IN (SELECT ID FROM B);

To delete these rows just use this

DELETE FROM A
WHERE B_ID NOT IN (SELECT ID FROM B);

Upvotes: 0

GMB
GMB

Reputation: 222482

You need where b.id is null rather than where b.id = null. In SQL, nothing is equal to null (nor different than null): to compare against null, you need special construct is [not] null.

I think that this is simpler expressed with not exists:

select a.*
from a
where not exists (select 1 from b where b.id = a.b_id)

If you wanted a delete statement:

delete from a
where not exists (select 1 from b where b.id = a.b_id)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

NOT EXISTS comes to mind:

select a.*
from a
where not exists (select 1 from b where b.id = a.b_id);

You can easily incorporate this into a delete if you want to remove the rows:

delete from a
    where not exists (select 1 from b where b.id = a.b_id);

BUT . . . you should really fix the problem moving forward by putting in a proper constraints. You seem to want a NOT NULL constraint and a FOREIGN KEY constraint.

Upvotes: 3

Related Questions