Let there be a users table with the user_id and user_name fields , and it contains entries with the same user_name values. The request to delete "extra" lines will look like, say:

DELETE FROM users WHERE user_id IN (SELECT user_id FROM users WHERE user_name IN (SELECT user_name FROM (SELECT user_id, COUNT(*) AS cnt, user_name FROM users GROUP BY user_name HAVING cnt>1) AS t1 ) AND user_id NOT IN (SELECT user_id FROM users GROUP BY user_name) )

Or so:

 DELETE FROM users WHERE user_id IN (SELECT t1.user_id FROM (SELECT user_id, user_name FROM users) AS t1 INNER JOIN (SELECT user_name, COUNT(*) AS cnt, MIN(user_id) AS min_id FROM users GROUP BY user_name HAVING cnt > 1) AS t2 ON t1.user_name=t2.user_name AND t1.user_id!=t2.min_id )

In general, whatever one may say, it turns out, in fact, two nested selects, and the operation time, in theory, respectively, is twice as long, although if it were possible to refer to the result t1 after AND in the first example, then it would be more Feng Shui, probably (and some crutches). Is there any way to fix this?

And second, for some reason, SQLyog swears at the above examples:

 Error Code: 1093 You can't specify target table 'users' for update in FROM clause

I’m lame in English, but as far as I understand, it claims that I can’t send the result of a request to the deletion command !? O_o

PS Strongly do not scold, sql did not remember since school years ...

  • one
    > And second, for some reason, SQLyog swears at the above examples: not SQLyog, but MySQL itself. Data update requests that include subqueries that work with the updated table are prohibited. In principle, you can trick MySQL and make another intermediate select wrapper. - etki
  • hashcode.ru/questions/193369/… - Yura Ivanov

2 answers 2

In the hope that user_id is the primary key:

 delete from users where user_id not in( select max(user_id) from users group by user_name);

This is a classic. user_id in the query can be replaced by rowid, if such a pseudocolumn exists in your DBMS.

  • Read the comments if you check laziness. > Data update requests that include subqueries that work with the updated table are prohibited. - Yura Ivanov
  • @Yura Ivanov, I used this request many times. - BuilderC
  • MySQL? sqlfiddle.com / #!2 / 04e35 > You can't specify the target table for users in FROM clause: - Yura Ivanov
  • The link to the circuit with an error is not created. sqlfiddle.com/#!2/4338fb uncomment need a request. - Yura Ivanov
  • @Yura Ivanov, no, not MySQL, but Oracle. - BuilderC 3:49 pm

Thank you all for your help. Although it has become obvious after the comments, just in case I will write a code that works with mysql (with the mentioned wrapper), it may be useful to whom:

 DELETE FROM users WHERE user_id NOT IN ( SELECT * FROM (SELECT MAX(user_id) FROM users GROUP BY user_name) AS t1 )