Merge duplicates and add value to existing value

**El_Heso** · Aug 28, 2012 23:36

Hi!
I hope someone know how to do this:
In my mysql database i have several posts that are duplicates ( only one field has a unique value )
example:

IDnr	Name	Place	Fruit
200	Tony	Sweden	Apple
201	Jessica	Sweden	Orange
200	Tony	Sweden	Orange
200	Tony	Sweden	banana

As above i want the data base to do this:

IDnr	Name	Place	Fruit
200	Tony	Sweden	Apple,Orange,Banana
201	Jessica	Sweden	Orange

Hope you guys know what i mean

if i need some script please guide me with how to do that in php

Regards Tony

**guido2004** · Aug 29, 2012 01:03

What you want is to normalize your database

Code:

Users table:

IDnr  Name      Place 	
200   Tony      Sweden 	
201   Jessica   Sweden 	

UserFruits table:

UserID    Fruit
200       Apple
200       Orange
200       Banana
201       Orange

**Antnee** · Aug 29, 2012 01:09

I forget the exact syntax, but something along these lines would return the values that you're looking for:

Code MySQL:

SELECT IDnr, Name, Place, GROUP_CONCAT(Fruit) FROM table_name GROUP BY Name;

I agree with Guido that you should really normalise the table however as you'll struggle if you need to select people with just "Orange", for example. However, if you didn't have that option, for whatever reason, I would select into another table, then when you're done get rid of the old table and rename the new one to the old one, if that makes sense. Something like:

Code MySQL:

INSERT INTO new_table (IDnr, Name, Place, Fruit)
     SELECT IDnr
          , Name
          , Place
          , GROUP_CONCAT(Fruit)
       FROM old_table
   GROUP BY Name;

That's kinda off the top of my head and not necessarily the best solution, but it should work

**El_Heso** · Aug 29, 2012 01:23

Hi!
thanks for the answer, but what i need is:
i have a excel with several duplicates except one column and i need to merge that column so i can import to a crm system without any duplicates

so i imported all in a mysql database tohandle this merge thing :-)

if i do as you guys said, is there a way to export all to one single row for each record so i can then import that file into the crm?

Hope you know what i mean

**El_Heso** · Aug 29, 2012 01:42

Thanks i tryed your code and that made it work exactly what i wanted

THANKS

Next problem :-)
now i have only unique rows, and i also have a csv file with people example:
IDnr, name, email, telephone

is there a way to import that into mysql and insert a new column with the value of "place" from the first table were Idnr is like the IDnr in this people table?

**r937** · Aug 29, 2012 04:38

i disagree that the data presented means that normalization should be done

granted, the sample data is overly simplistic, but suppose the table was intended to record where someone first tasted a fruit?

oh, and antnee, your GROUP BY is deficient, you'll get one row per person, but if the person had more than one country, you'll get an indeterminate one

**Antnee** · Aug 29, 2012 04:55

To be honest, looking at it again, I think I'd have left the schema the same, but removed the duplicates and made the IDnr and Fruit fields UNIQUE. Theoretically the Name field should relate back to a user via the IDnr field, judging by the test data, so I'd probably have removed the Name. Country? Hmmm, depends. Is this example recording what countries a particular person has tasted this particular fruit? If so then you could expect my own data to be something like:

Code:

Country | Fruit
--------+------------
UK      | Apple
UK      | Banana
UK      | Orange
USA     | Apple
USA     | Banana
Spain   | Pineapple

So, should the UNIQUE be on IDnr, Country and Fruit?

Don't understand exactly what the data is to be used for, but at least this way I could easily select fruits by country, fruits by user, countries by user etc.