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

Question

Hey,

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.

Answer 1

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.

Answer 2

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

Answer 3

Use the SQL "Exists" command.

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