CODEWITHSUNDEEP
sql
fmsf
fmsf

Reputation: 37137

In SQL, how to delete a row from one table if it doesn't have a corresponding row in another table?

How can I make:

DELETE FROM foo WHERE id=1 AND bar not contains id==1

To elaborate, how can I remove a row with id = 1, from table foo, only if there is not a row in table bar with id = 1.

Upvotes: 5

Views: 19863

Answers (3)

Drew
Drew

Reputation:

Use the SQL "Exists" command.

http://www.techonthenet.com/sql/exists.php

Upvotes: 0

nathan_jr
nathan_jr

Reputation: 9282

using a join: 

delete f
from   foo f
left
join   bar b on
       f.id = b.id 
where  f.id = 1 and
       b.id is null

Upvotes: 11

Ned Batchelder
Ned Batchelder

Reputation: 375574

DELETE FROM foo WHERE id=1 AND NOT EXISTS (SELECT * FROM bar WHERE id=1)

I'm assuming you mean that foo and bar are tables, and you want to remove a record from foo if it doesn't exist in bar.

Upvotes: 19

Related Questions