How to remove update duplicate comma separate ids using mysql single query?

Hi All,
After lot of R&D I made a below query for remove duplicate comma separate value in mysq table. I \
Hope it will help you 🙂

UPDATE dpx_catalog_product JOIN (SELECT `productID`,GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(sdbi_catalog_product.catID, ',', sub0.aNum), ',', -1)) AS idsFROM dpx_catalog_productINNER JOIN(    SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10 AS aSubscript    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units    CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens) sub0ON (1 + LENGTH(dpx_catalog_product.catID) - LENGTH(REPLACE(dpx_catalog_product.catID, ',', ''))) >= sub0.aNumGROUP BY productID)xON x.productID=dpx_catalog_product.productID
SET dpx_catalog_product.catID=x.ids

if you have any question please let me know!

Leave a Reply

Your email address will not be published. Required fields are marked *