title: How to merge duplicates rows in a MySQL table
body: <p>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. </p>
  
  <p>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.</p>
  
  <p>In order to explain this, I have put together a small example.</p>
  
  <pre><code>    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&amp;222&amp;444');
      INSERT INTO `tblProducts` VALUES ('2', 'Bradford', 'BR1 2HJ', '222');
      INSERT INTO `tblProducts` VALUES ('3', 'Tanner', 'TE4 9PO', '777&amp;333');
      INSERT INTO `tblProducts` VALUES ('4', 'Smythe', 'SM3 8KO', '111&amp;222');
      INSERT INTO `tblProducts` VALUES ('5', 'Francis', 'FL2 6HG', '444&amp;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&amp;555&amp;888');
      INSERT INTO `tblProducts` VALUES ('9', 'Barnes', 'BA5 5AB', '999');
      INSERT INTO `tblProducts` VALUES ('10', 'Smythe', 'SM1 4GE', '888&amp;777&amp;222');
  </code></pre>
  
  <p>If we run the following query, you will see that we have two duplicates, for Bradford and Tanner.</p>
  
  <pre><code>    SELECT Customer, Postcode, COUNT(*) FROM tblProducts group by Customer, Postcode having count(*) &gt; 1
  
      Customer    Postcode    COUNT(*)
      Bradford    BR1 2HJ     2
      Tanner      TE4 9PO     2
  </code></pre>
  
  <p>The separate duplicate records are:</p>
  
  <pre><code>    Customer  Postcode  Products
      Bradford  BR1 2HJ   111&amp;222&amp;444
      Bradford  BR1 2HJ   222
      Tanner    TE4 9PO   777&amp;333
      Tanner    TE4 9PO   555
  </code></pre>
  
  <p>I need to run a MySQL query to 'merge products where customer and postcode count > 1' as above, so the end result will be:</p>
  
  <pre><code>    Customer  Postcode  Products
      Bradford  BR1 2HJ   111&amp;222&amp;444
      Tanner    TE4 9PO   777&amp;333&amp;555
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Link to jsfiddle if it helps: <a href="http://sqlfiddle.com/#!9/966550/4/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/966550/4/0</a></p>
  
  <p>Can anyone help please as I am stuck.</p>
  
  <p>Many thanks,</p>
  
  <p>Rob</p>