MySQL Delete with Group By

Question

I am running a query successfully using in MySQL 5.5

SELECT columnA
FROM
  table
GROUP BY
  columnA
HAVING
  count(*) > 1

However, I need to run this same query using DELETE and I'm a little unsure how to delete ? i.e. the returned results should be deleted ?

Any ideas ?

Denis de Bernardy · Accepted Answer · 2011-06-09T18:13:04.237

40

Place it in a subquery:

delete from table 
where columnA in (
  select columnA
  from (
      select columnA
      from YourTable
      group by columnA
      having count(*) > 1
      ) t  
)

edited Jun 09 '11 at 18:13

answered Jun 09 '11 at 16:26

Denis de Bernardy

75,850
13
131
154

3

@Dennis - thanks for the help :) I'm getting "You can't specify target table 'table' for update in FROM clause" ? Using MySQL 5.5 ? – Tom Jun 09 '11 at 16:42
9

Oh, right. I always forget about MySQL's gotchas. :-) Subgroup it a second time to force it use a temp table: `delete from table where colA in (select colA from ([your thing]) t)` – Denis de Bernardy Jun 09 '11 at 16:45
@Dennis - ah :) that's what was getting me! Thanks a heap - you might want to update your answer :) – Tom Jun 09 '11 at 16:47
@Dennis - I think this actually deletes every row ? – Tom Jun 09 '11 at 18:06
As I read it, it should delete any row with a matching columnA value. In other words, all rows where columnA has a value which exists more than once in the table. – Denis de Bernardy Jun 09 '11 at 18:14
yeah that's right in theory ... but when I run it - it deletes ALL data in columnA ? – Tom Jun 09 '11 at 18:16
Yes, it deletes all matching records. If you're seeking to remove duplicates and keep a single one, you should be [doing this instead](http://stackoverflow.com/questions/6205640/sql-select-all-records-not-selected-by-another-query/6206575#6206575). – Denis de Bernardy Jun 09 '11 at 18:20
@Dennis - but it only should delete matching records where the count(*) > 1 ? [that is, if there are 2 records - it will delete 1 and keep the other?] I don't understand why it deletes everything instead ? – Tom Jun 09 '11 at 18:23
"that is, if there are 2 records - it will delete 1 and keep the other" -- no, your query is written so that it will delete both. See my last comment's link for a means to keep the first one. – Denis de Bernardy Jun 09 '11 at 18:26
so I have to put this inside the select ? delete from table where columnA in ( select columnA from ( ) ? – Tom Jun 09 '11 at 18:46
@Tom: if you want to only eliminate dups, see: http://stackoverflow.com/questions/6205640/sql-select-all-records-not-selected-by-another-query/6206575#6206575 – Denis de Bernardy Jun 09 '11 at 19:37
May i know why it needs an extra select query, but cannot just run with: delete from table where columnA in ( select columnA from YourTable group by columnA having count(*) > 1 ) – leo May 21 '21 at 03:48

score 3 · Answer 2 · answered Jun 09 '11 at 16:26

3

delete from YourTable
where
  YourTable.columnA 
  in 
  (select columnA
  from
    YourTable
  group by
    column A
  having
    count(*) > 1)

answered Jun 09 '11 at 16:26

GolezTrol

114,394
18
182
210

MySQL Delete with Group By

2 Answers2

Linked