CODEWITHSUNDEEP
sqlsql-server
psycho
psycho

Reputation: 1611

Delete all ID's that don't have a specific value

I have a table that I want to delete all the ids and there rows that do not have a specific value. For example say I have a table of houses and the have a primary key called house_id and I have another table called house_attributes that looks something like this.

#   house_id     Pet      Pet_Name   ...
1   1            cat      tigger
2   1            bird     tweety      
3   1            dog      rover
4   2            cat      Whiskers
5   2            bird     Polly
6   3            cat      Sylvester
7   3            bird     Juno
8   3            dog      Rex

From this table what is the best way to delete all house_ids that do not have a pet dog. So from the example above house_id 2 would meet that criteria so any instance of house_id 2 would be deleted from the table.

Thank you

Upvotes: 1

Views: 83

Answers (2)

Deep
Deep

Reputation: 3202

this maybe helpful :

DELETE FROM yourtable
WHERE  house_id NOT IN(SELECT house_id
                       FROM   yourtable
                       WHERE  Pet = 'dog')

the subquery will retrun those house_id which have pet dog and not in clause will ensure that delete except these.

Note: if you want to delete from both houses and house_attributes then same query should be run on houses table first then on house_attributes.

Upvotes: 3

Dan Bracuk
Dan Bracuk

Reputation: 20804

This sort of construct will help.

delete from houses
where house_id in
(select house_id
from houses
except
select house_id
from pets
where pet = 'dog'
)

However, it's a strange plan. Once those records are gone you can't use them anymore. Another option is to keep your records and simply select the ones that do have pet dogs.

Upvotes: 1

Related Questions