CODEWITHSUNDEEP
sql
nacho10f
nacho10f

Reputation: 5886

Delete items from table without a reference ID

I need to delete all the Contributors that don't have a License assigned to them

Table        : Columns

Contributors : [id, Name,...]
Licenses     : [id, ContributorId, Name, ...]

Something like this

DELETE FROM Contributors
WHERE
License.ContributorId != Contributor.Id

Upvotes: 3

Views: 218

Answers (3)

Nico
Nico

Reputation: 496

 DELETE FROM Contributors
    WHERE Contributors.Id NOT IN 
    (SELECT DISTINCT License.ContributorId FROM License)

Upvotes: 1

Abe Miessler
Abe Miessler

Reputation: 85056

I believe this will work if you are using SQL Server:

DELETE Contributors 
FROM Contributors as c
LEFT JOIN License as l
ON c.id = l.ContributorID
WHERE l.id is null

A good test to do before actually doing the delete is to replace the DELETE Contributors line with SELECT *. This will show you all the records that are about to be deleted, so it's a nice sanity check...

So your sanity check would look like this:

SELECT *
FROM Contributors as c
LEFT JOIN License as l
ON c.id = l.ContributorID
WHERE l.id is null

Upvotes: 2

Rob Packwood
Rob Packwood

Reputation: 3798

DELETE FROM Contributors
WHERE NOT EXISTS (
  SELECT *
  FROM License
  WHERE License.ContributorId = Contributors.Id)

Upvotes: 3

Related Questions