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 sdbi_catalog_product
JOIN
(SELECT `productID`,GROUP_CONCAT(DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(sdbi_catalog_product.catID, ',', sub0.aNum), ',', -1)) AS ids
FROM sdbi_catalog_product
INNER 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
) sub0
ON (1 + LENGTH(sdbi_catalog_product.catID) - LENGTH(REPLACE(sdbi_catalog_product.catID, ',', ''))) >= sub0.aNum
GROUP BY productID)x
ON x.productID=sdbi_catalog_product.productID
SET sdbi_catalog_product.catID=x.ids
Great job
ReplyDelete