MySQL – Update CONCAT Without Duplicates

concatMySQLupdate

I would like to concatenate multiple values ​​and remove duplicates (case-insensitive) in the same field with SQL only.

Without using a unique key or stored procedure.

Ex.

Toto;toto1;titi;TOTO;toto1;tata;

in

Toto;toto1;titi;TOTO;tata;

Here is my SQL model:

UPDATE users SET pseudo = CONCAT_WS(';', pseudo, 'NEWS_VALUE') WHERE id = '1';

I can not handle duplicates

Thank you for your help

Best Answer

You need to create a sub-query with distinct values and then join it to your main table and update, like this:

You will need a temporary numbers table:

CREATE TABLE numbers (
  n INT PRIMARY KEY);

INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

UPDATE users u 
JOIN (
     SELECT
     ID, GROUP_CONCAT(pseudo SEPARATOR ';') pseudo
     FROM(
     SELECT DISTINCT
      users.id,
      lower(SUBSTRING_INDEX(SUBSTRING_INDEX(users.pseudo, ';', numbers.n), ';', -1)) pseudo
    FROM
      numbers INNER JOIN users
      ON CHAR_LENGTH(users.pseudo)
         -CHAR_LENGTH(REPLACE(users.pseudo, ';', ''))>numbers.n-1
    ORDER BY
      id, n) u
    GROUP BY ID
     ) du 
    ON u.id = du.id
SET u.pseudo = CONCAT_WS(';', du.pseudo, 'NEWS_VALUE') 
WHERE u.id = '1';

SQL Fiddle that shows how to split using numbers table

Related Question