How to delete repeated value from all the row present in a column of mysql

Question

I am stuck in a situation where in my table I have multiple duplicate value for each row in a column, and table look like

User_Id    |   Color
-----------+-------------------------------
1          |  Red, Blue, Red,Green
2          |  Green,Green,Blue,Blue, Red
3          |  Black, White
4          |  Red,Red,Red

I want to remove or delete each duplicate value from column color, so that each user_id hold only unique value like.

User_Id    |   Color
-----------+--------------------
1          | Red, Blue,Green
2          | Green, Blue, Red
3          | Black,White
4          | Red

Is there any way to achieve the desired output? I searched a lot but got nothing.

Your valuable comment will be highly appreciated.

@ Strawberry, by using CONCAT_WS from merging the two coloumn value — San Cube, Mar 15 '19 at 12:08
You should normalize, a delimited list can't enforce uniqueness: no way to prevent `Red, Blue, Red, Green, Blue` — Raymond Nijland, Mar 15 '19 at 12:27
@SanCube i've posted a answer want you need to do every time when working with a delimited list.. Also when you want summery data meaning counting how many color red is in there.. — Raymond Nijland, Mar 15 '19 at 12:48

score 0 · Accepted Answer · edited Apr 20 '19 at 15:53

is there any way to achieve the desired output, I searched a lot but got nothing. your valuable comment will be highly appreciated.

Like I already said you should normalize, a delimited list can't enforce uniqueness: no way to prevent Red, Blue, Red, Green, Bluewhen inserting and updating without writing application code or a trigger. Which also means fetching the complete data.

If you don't normalize this you are going to need to be creative, with a SQL number generator and nested SUBSTRING_INDEX() functions and a CROSS JOIN to split the string. And use GROUP BY and GROUP_CONCAT(DISTINCT ..) to make the unique values

You don't want to do this, this query shows how hard the query is on a delimited list

Query

SELECT 
 DISTINCT  
     t.User_Id
    , GROUP_CONCAT(DISTINCT TRIM(SUBSTRING_INDEX(
          SUBSTRING_INDEX(
              t.Color
            , ','
            , sql_number_generator.number
          )
          , ','
          , -1
        )
     )) AS color
FROM (

SELECT 
 @row := @row + 1 AS number
FROM (
  SELECT 0 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
) row1
CROSS JOIN (
  SELECT 0 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
) row2
CROSS JOIN (
  SELECT @row := 0 
) init_user_params 

) AS sql_number_generator
CROSS JOIN 
 t
GROUP BY
 t.User_Id

Result

| User_Id | color          |
| ------- | -------------- |
| 1       | Blue,Green,Red |
| 2       | Blue,Green,Red |
| 3       | Black,White    |
| 4       | Red            |

see demo

but the problem is how to update the color column with this result, i tried but it throw an error [ Operand should contain 1 column(s) ], i can't understand how it is to be done

Still can't believe you are willing to continue this approach. after the warnings

UPDATE 
 t 
INNER JOIN (

SELECT 
 DISTINCT  
     t.User_Id
    , GROUP_CONCAT(DISTINCT TRIM(SUBSTRING_INDEX(
          SUBSTRING_INDEX(
              t.Color
            , ','
            , sql_number_generator.number
          )
          , ','
          , -1
        )
     )) AS color
FROM (

SELECT 
 @row := @row + 1 AS number
FROM (
  SELECT 0 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
) row1
CROSS JOIN (
  SELECT 0 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
) row2
CROSS JOIN (
  SELECT @row := 0 
) init_user_params 

) AS sql_number_generator
CROSS JOIN 
 t
GROUP BY
 t.User_Id

) AS records_to_updated

SET t.Color = records_to_updated.color

WHERE 
  t.User_Id =  records_to_updated.User_Id

see demo

hats off sir, this query exactly gives what i want, but the problem is how to update the color coloumn with this result, i tried but it throw an error [ Operand should contain 1 column(s) ], i can't understand how it is to be done — San Cube, Mar 15 '19 at 13:30
@SanCube the INSERT and keeping unique values is even harder not going to show you that also, i have are already have sinned way to much with this code. — Raymond Nijland, Mar 15 '19 at 13:48
its ok sir, and thanx once again for such a beautiful solution. — San Cube, Mar 15 '19 at 13:51

How to delete repeated value from all the row present in a column of mysql

1 Answers1