I want to delete a row if a particular x value matches a y value in any row in the same table.
Ex:
| x | y | | 4 | 2 | | 2 | 6 | | 8 | 1 | | 3 | 1 | | 7 | 8 | | 9 | 5 | would become:
| x | y | | 4 | 2 | | 3 | 1 | | 7 | 8 | | 9 | 5 | 
- And what have you tried so far? Which flavor of SQL are you using?SMA– SMA2016-09-12 15:51:47 +00:00Commented Sep 12, 2016 at 15:51
Add a comment |
3 Answers
Use EXISTS
Delete from yourtable where exists (select 1 from tab b where b.y =yourtable.x) 
Comments
If your DB allows it, a self-join may work:
DELETE FROM foo AS xside LEFT JOIN foo AS yside ON xside.y = yside.x 
3 Comments
MatBailie
Some RDBMS don't like the join being able to cause the row to be deleted more than once. In which case a sub query, for yside, with a GROUP BY or a DISTINCT could help.
Marc B
for mysql, this'd be the only way to do it. you can't do a (sub)select on a table you're modifying, but a join works.
MatBailie
Sorry, I meant
LEFT JOIN (subquery) AS ysideDelete from tab where x in ( select y from tab) Alternate version to counter null values in y column.
Delete from tab t where exists ( select 1 from tab ta where ta.y = t.x) 3 Comments
MatBailie
@Matt - the
NULL issue is, in my opinion, the strongest reason to avoid IN. Some RDBMS optimise better than others, but all will bite you int he list is NULLable.
Akshey Bhat
@Matt thanks for your observation. Edited my answer to include alternate version of the query