How to remove duplicate rows in two columns with group by?

Question

I'm trying remove the duplicate rows using this query but MySQL don't return nothing and crash:

DELETE FROM project_category WHERE prc_id IN (SELECT prc_id
FROM project_category
GROUP BY prc_proid, prc_catid
HAVING COUNT(*) > 1)

I want remove the duplication:

+--------+-----------+-----------+
| prc_id | prc_proid | prc_catid |
+--------+-----------+-----------+
|   1691 |       207 |        16 |
|   1692 |       207 |        16 |
+--------+-----------+-----------+

Also please show us a sample of the rows you want to delete (and some you don't) so we can better understand the situation. Right now we're blind, we don't know what data it is we're trying to manipulate — ADyson, Apr 26 '19 at 19:39
Possible duplicate: https://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table — Gen Wan, Apr 26 '19 at 19:42

score 1 · Accepted Answer · answered Apr 26 '19 at 19:43

1

MySql does not allow direct reference to the table where the DELETE takes place in the WHERE clause. Do it like this:

DELETE FROM project_category 
WHERE prc_id IN (
  SELECT prc_id FROM (
    SELECT prc_id
    FROM project_category
    GROUP BY prc_proid, prc_catid
    HAVING COUNT(*) > 1
  ) t
)

answered Apr 26 '19 at 19:43

forpas

160,666
10
38
76

Thanks! Works perfect. – NAG Apr 26 '19 at 19:44
Now it is syntactically correct but be aware that the value returned for `prc_id` is not guaranteed to be what you would expect since it is not included in the `group by` clause and it is not aggregated. – forpas Apr 26 '19 at 19:51
It will be disallowed in MySQL 8.0. Cf `ONLY_FULL_GROUP_BY`. – Rick James May 17 '19 at 22:13
The query will delete only one dup row. That is, if there are 3 dups, 2 will remain after the `DELETE`. – Rick James May 17 '19 at 22:14

How to remove duplicate rows in two columns with group by?

1 Answers1