How to delete NON-duplicate rows from a table?

Question

I have a table single_post which looks something like below

id | for_post
0   50
1   50
2   100
3   75

How to delete every entry on which for_post value exist only once ?. Which means after executing the query, the last two entries will be removed because for_post exist only once.

I didn't try anything yet because I have no idea what to try. Thanks in advance

possible duplicate of [how to delete duplicates on mysql table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table) — symcbean, Feb 02 '15 at 21:45
I dont want to delete duplicate rows, instead, i want to delete non-duplicate rows. @symcbean — Rafik Bari, Feb 02 '15 at 21:46

Oferch · Answer 1 · 2015-02-02T22:01:10.017

0

How About Something like this:

DELETE FROM single_post
WHERE for_post IN
(SELECT for_post FROM single_post GROUP BY for_post HAVING count(for_post)=1)

edited Feb 02 '15 at 22:01

answered Feb 02 '15 at 21:53

Oferch

71
7

#1093 - You can't specify target table 'single_post' for update in FROM clause – Rafik Bari Feb 02 '15 at 22:04
Then duplicate the table anddo the inner select on the duplicated table – Oferch Feb 02 '15 at 22:09

How to delete NON-duplicate rows from a table?

1 Answers1