CODEWITHSUNDEEP
mysqlgroup-by
Tom
Tom

Reputation: 253

MySQL Delete with Group By

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 ?

Upvotes: 24

Views: 33765

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

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  
)

Upvotes: 41

GolezTrol
GolezTrol

Reputation: 116110

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

Upvotes: 3

Related Questions