MySql Deletion of Row Data Where Count > 1

Ridgejp
Ridgejp used Ask the Experts™
on
Hi,

Just to want an easy way to thin out duplication in a table. I have column1, column2 and column3 - where the count of column1 is greater than 1 then I need a select statement to perform a delete query that limits the deletion to 1.

Any thoughts?

J
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Why would you have duplicate content, could you elaborate...
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I usually do this in two steps

c
reate temporary table _temp (id, total);

insert into _temp (id, total) select max(id), count(*) from targetTable group by id;

delete from targetTable where id not in (select id from _temp);

Open in new window


It is possible to use sub queries as well - it all depends on where you want to run this from and how often.

Author

Commented:
Thanks Julian. Exactly what I needed.

J
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You are welcom

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial