SQL Delete Query

Question

I need to write an SQL script that selects one record in table1, then does a lookup in the remaining tables in the database. If it doesn't find the record, I need delete the record from table1. Anyone provide some sample script?

Answer 1

One example

delete table1
where not exists (select 1 
                   from Table2 
                   where table1.SomeColumn = Table2.SomeColumn)
AND table1.SomeColumn = 5 --just an example, 

Leave the AND out if you want to delete all the rows from table 1 that do not exist in table 2

you can also use LEFT JOIN or NOT IN

Answer 2

I have done things like this:

DELETE table1
  FROM table1
 WHERE table1.ID NOT IN (
       SELECT RefID FROM Table2
       UNION
       SELECT RefID FROM Table3
       ...
       )

Assuming RefID are FK's to table1.ID. Is this what you need?

Answer 3

DELETE FROM Table1 WHERE id=10 AND NOT EXISTS (SELECT * FROM Table2 WHERE id=10);

Answer 4

Very generally, (since you gave little details)

Delete Table1 t1
Where [Criteria to find table1 Record]
  And Not Exists(Select * From Table2
                 Where pk = t1.Pk)
  And Not Exists(Select * From Table3
                 Where pk = t1.Pk)
  And Not Exists(Select * From Table4
                 Where pk = t1.Pk)
  ... etc. for all other tables