SQL: Delete records based on values in another column

Question

Please see my fiddle for the example:

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....

Gordon Linoff · Accepted Answer · 2013-06-19 19:22:57Z

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.

Collectives™ on Stack Overflow

SQL: Delete records based on values in another column

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related