CODEWITHSUNDEEP
sqlsql-serversql-delete
neuquen
neuquen

Reputation: 4189

SQL: Delete records based on values in another column

Please see my fiddle for the example:

http://sqlfiddle.com/#!3/1aaea/1

My goal is to delete all vendors that have no migrate values of 'Y'. So in the case of my fiddle, only vendors B and D should be deleted. I'm struggling with how to delete all vendors where migrate='n', but keep vendors that have migrate='y' EVEN IF they have values of migrate='n'.

How can I include vendors that have migrate='y' and exclude vendors that don't?

I feel like I am thinking too hard and the answer is right under my nose....

Upvotes: 0

Views: 333

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can do this by testing for the condition in a where clause:

delete from example
    where not exists (select 1
                      from example e2
                      where e2.vendor = example.vendor and
                            e2.migrate = 'Y'
                     );

To test this in SQLFiddle, you need to put the delete in the left window (the schema window), rather than in the query window.

Upvotes: 1

Related Questions