Finding mySQL duplicates, then merging data

Question

I have a mySQL database with a tad under 2 million rows. The database is non-interactive, so efficiency isn't key.

The (simplified) structure I have is:

`id` int(11) NOT NULL auto_increment
  `category` varchar(64) NOT NULL
  `productListing` varchar(256) NOT NULL

Now the problem I would like to solve is, I want to find duplicates on productListing field, merge the data on the category field into a single result - deleting the duplicates.

So given the following data:

+----+-----------+---------------------------+
| id | category  | productListing            |
+----+-----------+---------------------------+
|  1 | Category1 | productGroup1             | 
|  2 | Category2 | productGroup1             | 
|  3 | Category3 | anotherGroup9             | 
+----+-----------+---------------------------+

What I want to end up is with:

+----+----------------------+---------------------------+
| id | category             | productListing            |
+----+----------------------+---------------------------+
|  1 | Category1,Category2  | productGroup1             | 
|  3 | Category3            | anotherGroup9             | 
+----+----------------------+---------------------------+

What's the most efficient way to do this either in pure mySQL query or php?

soulmerge · Accepted Answer · 2010-05-24 11:32:15Z

2

I think you're looking for GROUP_CONCAT:

SELECT GROUP_CONCAT(category), productListing
FROM YourTable
GROUP BY productListing

I would create a new table, inserting the updated values, delete the old one and rename the new table to the old one's name:

CREATE TABLE new_YourTable SELECT GROUP_CONCAT(...;
DROP TABLE YourTable;
RENAME TABLE new_YourTable TO YourTable;
-- don't forget to add triggers, indexes, foreign keys, etc. to new table

edited May 24, 2010 at 11:32

answered May 24, 2010 at 11:25

soulmerge

76.3k20 gold badges122 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michael Pasqualone Over a year ago

This seems to be doing what I want, I guess the next question is - is storing this type of data in a comma separated list the right way to go about things? I'm thinking it may make more sense to create a new table, but unless there is a benefit I won't really bother.

ovais.tariq Over a year ago

Storing the data in a comma seperated list is not the right way to go. A column should have one value. Otherwise updates would be very inefficient.

Quassnoi · Accepted Answer · 2010-05-24 11:28:41Z

0

SELECT  MIN(id), GROUP_CONCAT(category SEPARATOR ',' ORDER BY id), productListing
FROM    mytable
GROUP BY
        productListing

answered May 24, 2010 at 11:28

Quassnoi

428k94 gold badges628 silver badges624 bronze badges

Collectives™ on Stack Overflow

Finding mySQL duplicates, then merging data

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related