MYSQL Remove duplication [closed]

Quick try.

You can do this to add all the phone numbers to the first record (comma separated, you can add a parm to change this to a slash instead but this would be an even worse idea)

UPDATE test a
INNER JOIN (SELECT name, address, GROUP_CONCAT(phone) AS AllPhone, MIN(id) AS MinId FROM test GROUP BY name, address) b
ON a.name = b.name
AND a.address = b.address
AND a.id = b.MinId
SET a.phone = b.AllPhone

Then delete the others with something like this

DELETE a
FROM test a
LEFT JOIN (SELECT name, address, MIN(id) AS MinId FROM test GROUP BY name, address) b
ON a.id = b.MinId
WHERE b.MinId IS NULL

Note that this going to give you a delimited list of phone numbers for a name / address which is a major pain to deal with in future, and a sign of a badly designed database.

Browse More Popular Posts

Leave a Comment