MYSQL Remove duplication [closed]

by

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.

More Related Contents:

  1. With MySQL, how can I generate a column containing the record index in a table?
  2. MySQL and GROUP_CONCAT() maximum length
  3. What data type to use for hashed password field and what length?
  4. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  5. MySQL pagination without double-querying?
  6. MySQL convert date string to Unix timestamp
  7. How to speed up SELECT .. LIKE queries in MySQL on multiple columns?
  8. Using an Alias in SQL Calculations
  9. MySql Table Insert if not exist otherwise update
  10. How to get difference between two dates in months using MySQL query?
  11. How to change the foreign key referential action? (behavior)
  12. MySQL – Control which row is returned by a group by
  13. Can MySQL replace multiple characters?
  14. Docker (Apple Silicon/M1 Preview) MySQL “no matching manifest for linux/arm64/v8 in the manifest list entries”
  15. My MySQL trigger doesn’t work, simple syntax, not complicated
  16. Using ORDER BY and GROUP BY together
  17. How can VBA connect to MySQL database in Excel?
  18. MySQL Pivot Table Column Data as Rows
  19. updating columns with a sequence number mysql
  20. How repair corrupt xampp ‘mysql.user’ table?
  21. Select a column with a keyword name
  22. Sum results of a few queries and then find top 5 in SQL
  23. Select all months within given date span, including the ones with 0 values
  24. ERROR 1356 (HY000): View ‘mysql.user’ references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  25. Use Composite Primary Key as Foreign Key
  26. ALTER TABLE ADD COLUMN takes a long time
  27. Creating MYSQL Procedure in Laravel 4 Migrations
  28. MySQL: Computed Column
  29. Mysql server does not support 4-byte encoded utf8 characters
  30. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)

Leave a Comment