Is there any way to remove rows from column X when Y="a"? Eg. Given:
| X | Y |
|---|---|
| 100 | a |
| 101 | a |
| 101 | b |
| 200 | c |
The end result would be only row where [200, c] exist.
[101, b] is also removed as 101 is also in a. Thank you!
CodePudding user response：
I don't know your RDBMS but I think this is ANSI standard SQL. To retrieve the rows you want, you can use a simple subquery:
SELECT X, Y FROM TBL WHERE X NOT IN
(SELECT X FROM tbl WHERE Y = 'a')
If you want to actually remove (DELETE) rows, you can simply DELETE with the same subquery:
DELETE FROM tbl WHERE X IN
(SELECT X FROM tbl WHERE Y = 'a')
Since you did not include the table name, I just made one up (tbl).