CODEWITHSUNDEEP
sqlsql-server-2005
jerle78
jerle78

Reputation: 157

SQL Delete Query

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?

Upvotes: 0

Views: 278

Answers (4)

Charles Bretana
Charles Bretana

Reputation: 146603

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

Upvotes: 1

Sjoerd
Sjoerd

Reputation: 75689

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

Upvotes: 1

n8wrl
n8wrl

Reputation: 19765

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?

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135181

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

Upvotes: 3

Related Questions