0

I want to drop all rows in a table of mySQL that have a duplicate using GROUP BY. My table has fields name date position email and looks like

+----------+---------------+----------+--------------------+
| M        | 1976-10-03    |        1 | m@gmail            |
| R        | 1982-03-26    |        2 | r@gmail.com        |
| C        | 1987-09-03    |        3 | c@gmail.com        |
| M        | 1976-10-03    |        1 | m@gmail            |
+----------+---------------+----------+--------------------+

I want to get

+----------+---------------+----------+--------------------+           |
| R        | 1982-03-26    |        2 | r@gmail.com        |
| C        | 1987-09-03    |        3 | c@gmail.com        |
+----------+---------------+----------+--------------------+

My attempt (from the answers to similar questions)

DELETE FROM ts1 WHERE * IN (SELECT * FROM ts1 GROUP BY * HAVING COUNT(*)>1);

Where are the errors? I understand I'm using too many * but I want to avoid naming all columns because they are too many in my actual table. Notice that I want to check for duplicates over the entire row.

TEX
  • 2,249
  • 20
  • 43

1 Answers1

0

You can't use GROUP BY * - you want to use GROUP BY name:

DELETE FROM ts1 WHERE name IN (SELECT name FROM ts1 GROUP BY name HAVING COUNT(*)>1);

Note that this would assume that users have unique names.

So you may actually want to check their emails instead:

DELETE FROM ts1 WHERE email IN (SELECT email FROM ts1 GROUP BY email HAVING COUNT(*)>1);
Eric Hotinger
  • 8,957
  • 5
  • 36
  • 43
  • Is there a way to check all fields without naming all of them? – TEX Oct 30 '15 at 18:18
  • @user3285148 Typically by that definition you are not grouping anything. In that case a `SELECT * FROM table` is equivalent to `SELECT * FROM table GROUP BY *` ... unless you have pure duplicates across the entire row. – Eric Hotinger Oct 30 '15 at 18:19
  • I have duplicates over the entire row. – TEX Oct 30 '15 at 18:23
  • @user3285148 - well there are no shortcuts to list them all out AFAIK. You can't say `GROUP BY *`, you have to list out every column. I'd get to typing if you have a lot of columns and need to check every column. – Eric Hotinger Oct 30 '15 at 18:28
  • If I type your command I get the following error ERROR 1093 (HY000): You can't specify target table 'ts1' for update in FROM clause – TEX Oct 30 '15 at 19:20
  • And, could you write the command by listing explicitly all the fields, not just the email field? – TEX Oct 30 '15 at 19:21
  • For future reference: In case you get "ERROR 1093 (HY000): You can't specify target table 'ts1' for update in FROM clause" You can use something like this to avoid it DELETE FROM ts1 WHERE name IN (SELECT name FROM (SELECT * FROM ts1) AS x GROUP BY name HAVING COUNT(*)>1); –  Sep 23 '17 at 17:15