SQL delete row where one value matches that a value in a DIFFERENT column

Question

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, Sep 12 '16 at 15:51

score 2 · Answer 1 · answered Sep 12 '16 at 15:54

2

Use EXISTS

Delete from 
yourtable where exists (select 1 from tab b where b.y =yourtable.x)

answered Sep 12 '16 at 15:54

Pரதீப்

score 1 · Answer 2 · answered Sep 12 '16 at 15:52

1

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

answered Sep 12 '16 at 15:52

Marc B

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. – MatBailie Sep 12 '16 at 16:07
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. – Marc B Sep 12 '16 at 16:09
Sorry, I meant `LEFT JOIN (subquery) AS yside` – MatBailie Sep 12 '16 at 16:10

Akshey Bhat · Answer 3 · 2016-09-12T16:13:17.523

1

Delete 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)

edited Sep 12 '16 at 16:13

answered Sep 12 '16 at 15:52

Akshey Bhat

http://stackoverflow.com/questions/32175497/mysql-not-in-query-3-tables – Matt Sep 12 '16 at 16:03
@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. – MatBailie Sep 12 '16 at 16:09
@Matt thanks for your observation. Edited my answer to include alternate version of the query – Akshey Bhat Sep 12 '16 at 16:15

3 Answers3