CODEWITHSUNDEEP
mysqlduplicates
Rob Wassell
Rob Wassell

Reputation: 81

How to merge duplicates rows in a MySQL table

I've almost finished a project involving customers and products and only identified at the end that we have duplicate records due to keying errors, where sales staff have added the same customer to the database more than once.

What I need to do is to identify the duplicate records by comparing Customer name and their Postcode and merge the Products so that the resulting updated products field is consistent with all of the products that are applicable to them, but only one customer record exists.

In order to explain this, I have put together a small example.

    DROP TABLE IF EXISTS `tblProducts`;
    CREATE TABLE `tblProducts` (
      `ID` int(10) DEFAULT NULL,
      `Customer` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `Postcode` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `Products` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

    INSERT INTO `tblProducts` VALUES ('1', 'Bradford', 'BR1 2HJ', '111&222&444');
    INSERT INTO `tblProducts` VALUES ('2', 'Bradford', 'BR1 2HJ', '222');
    INSERT INTO `tblProducts` VALUES ('3', 'Tanner', 'TE4 9PO', '777&333');
    INSERT INTO `tblProducts` VALUES ('4', 'Smythe', 'SM3 8KO', '111&222');
    INSERT INTO `tblProducts` VALUES ('5', 'Francis', 'FL2 6HG', '444&333');
    INSERT INTO `tblProducts` VALUES ('6', 'Tanner', 'TE4 9PO', '555');
    INSERT INTO `tblProducts` VALUES ('7', 'Peters', 'PE4 4PE', '444');
    INSERT INTO `tblProducts` VALUES ('8', 'Jeffrey', 'JE9 4JK', '444&555&888');
    INSERT INTO `tblProducts` VALUES ('9', 'Barnes', 'BA5 5AB', '999');
    INSERT INTO `tblProducts` VALUES ('10', 'Smythe', 'SM1 4GE', '888&777&222');

If we run the following query, you will see that we have two duplicates, for Bradford and Tanner.

    SELECT Customer, Postcode, COUNT(*) FROM tblProducts group by Customer, Postcode having count(*) > 1

    Customer    Postcode    COUNT(*)
    Bradford    BR1 2HJ     2
    Tanner      TE4 9PO     2

The separate duplicate records are:

    Customer  Postcode  Products
    Bradford  BR1 2HJ   111&222&444
    Bradford  BR1 2HJ   222
    Tanner    TE4 9PO   777&333
    Tanner    TE4 9PO   555

I need to run a MySQL query to 'merge products where customer and postcode count > 1' as above, so the end result will be:

    Customer  Postcode  Products
    Bradford  BR1 2HJ   111&222&444
    Tanner    TE4 9PO   777&333&555

Note that there is only one instance of 222 in the first record as 222 already existed. The duplicate record will be removed from the MySQL table so that only one record exists.

I must admit, I had assumed this would be easy for MySQL to achieve and have spent ages researched merging rows, merging fields, removing duplicates and not found anything that seems to specifically to help.

Link to jsfiddle if it helps: http://sqlfiddle.com/#!9/966550/4/0

Can anyone help please as I am stuck.

Many thanks,

Rob

Upvotes: 0

Views: 1249

Answers (1)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

SELECT TP.Customer,TP.Postcode,TP.Products
FROM tblProducts TP
INNER JOIN
(
    SELECT MIN(ID) ID FROM tblProducts GROUP BY Customer, Postcode
)INNERTABLE  ON INNERTABLE.ID=TP.ID

You can try above query.

Upvotes: 1

Related Questions