The best way to remove value from SET field?

Question

Which is the best way to update a mysql SET field, to remove a specific value from the field.

Eg. field categories with values: 1,2,3,4,5? I want to remove '2' from the list:

UPDATE table 
SET categories = REPLACE(categories, ',2,', ',') 
WHERE field LIKE '%,2,%';

But what if '2' is the first or the last value from the list?

UPDATE table 
SET categories = REPLACE(categories, '2,', '') 
WHERE field LIKE '2,%';

UPDATE table 
SET categories = REPLACE(categories, ',2', '') 
WHERE field LIKE ',2%';

How could I handle all 3 cases with one single query?!

Execute all the 3 queries one by one, if 2 is first only 2nd query will update the row rest of queries will run but will have no effect on you database and so on. — Ankit, Feb 01 '13 at 09:00
This cannot be a duplicate of the suggested question - the other question refers to PHP code, not mysql. — Jon Egerton, Feb 01 '13 at 14:55

fthiella · Accepted Answer · 2013-02-01T09:42:46.393

33

If the value you need to remove from the set can't be present more than once, you could use this:

UPDATE yourtable
SET
  categories =
    TRIM(BOTH ',' FROM REPLACE(CONCAT(',', categories, ','), ',2,', ','))
WHERE
  FIND_IN_SET('2', categories)

see it working here. If the value can be present more than once, this will remove all occourences of it:

UPDATE yourtable
SET
  categories =
    TRIM(BOTH ',' FROM
      REPLACE(
        REPLACE(CONCAT(',',REPLACE(col, ',', ',,'), ','),',2,', ''), ',,', ',')
    )
WHERE
  FIND_IN_SET('2', categories)

edited Feb 01 '13 at 09:42

answered Feb 01 '13 at 09:26

fthiella

48,073
15
90
106

1

ENUMs and SETs can be manipulated with bit operators much more efficiently. See @palindrom's answer below. – Aleš Krajník Aug 12 '16 at 16:20

score 10 · Answer 2 · edited Jul 28 '16 at 16:36

10

update TABLE
set COLUMN = COLUMN & ~NUM
where COLUMN & NUM

Taken from comments section of: http://dev.mysql.com/doc/refman/5.0/en/set.html

Beware though, NUM is not the value '2' but its internal index. So if you defined the field like "set ('1,'2','3','4','5')" then the corresponding indexes of these values are (1,2,4,8,16).

edited Jul 28 '16 at 16:36

Aleš Krajník

177
1
10

answered Feb 01 '13 at 11:03

palindrom

18,033
1
21
37

score 5 · Answer 3 · edited Jun 20 '20 at 09:12

5

The best way is not to save values separated by a comma on the table.

But to answer your question, you can use CASE on this,

UPDATE table 
SET categories = CASE WHEN field LIKE '%,2,%'  -- In the middle
                           THEN REPLACE(categories, ',2,', ',')
                      WHEN field LIKE '2,%'    -- At the beginning
                           THEN REPLACE(categories, '2,', '')
                      WHEN field LIKE '%,2'    -- At the end
                           THEN REPLACE(categories, ',2', '') 
                      WHEN field = '2'         -- At whole
                           THEN '' 
                 END
WHERE FIND_IN_SET('2', categories)

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 01 '13 at 09:03

John Woo

258,903
69
498
492

2

Why not using values separated by a comma on the table? In some cases this can be faster than a join. – morandi3 Feb 01 '13 at 09:23
join can use index. this won't. – John Woo Feb 01 '13 at 09:24
2

there's a small typo on `field LIKE ',2%'` which should be `field LIKE '%,2'`, and I would also use `WHERE FIND_IN_SET('2', field)` or `WHERE CONCAT(',', field, ',') LIKE '%,2,%'` otherwise the where condition will match 22 also... but it's a nice solution! – fthiella Feb 01 '13 at 10:06
1

@fthiella I corrected the typo + added handling for the case, where field equals to the string in question. – Vladislav Rastrusny Mar 05 '15 at 08:20

score 3 · Answer 4 · answered Oct 13 '16 at 09:22

Here is another way of doing this:

UPDATE table
SET categories = CONCAT_WS(',',
    IF(FIND_IN_SET('1', categories), '1', NULL),
    -- Note that '2' is missing here!
    IF(FIND_IN_SET('3', categories), '3', NULL),
    IF(FIND_IN_SET('4', categories), '4', NULL),
    IF(FIND_IN_SET('5', categories), '5', NULL)
);

CONCAT_WS concatenates all its arguments (except argument 1) with the first argument (in this case ','), if they are not NULL. We look for each possible value of the SET field if the field contains it but skip the one we want to remove (2 in this case). If yes, we return that value, otherwise NULL. This concatenates all values of the set with ',', skipping the one we want to delete, reconstructing a new value for the SET field.

This of course only works, if you know all possible values of categories, but since this is a SET field, you know that.

Courtney Miles · Answer 5 · 2018-06-07T00:01:09.287

field categories with values: 1,2,3,4,5? I want to remove '2' from the list:

I find that using a bitwise operator to be the cleanest way. The return value of FIND_IN_SET() returns the position of the value so you can use this calculate the bit to turn off.

UPDATE table
SET categories = CAST(categories AS UNSIGNED) & ~POW(2, FIND_IN_SET('2', categories))
WHERE FIND_IN_SET('2', categories);

Note that (categories+0) is to force the current SET value to an integer. You may find this is redundant, but be careful with sets that contain a lot of values. I found that the resulting maths can be wrong if you don't explicitly force the set to an integer -- you definitely don't want the wrong value when performing an operation like this.

This is actually the best solution. However, I had to write like `~POW(2, FIND_IN_SET('2', categories) - 1)` to make it work properly, otherwise it was keeping the value '2' and removing the value after it from the set. — Nikunj Bhatt, Sep 01 '19 at 16:56

tim · Answer 6 · 2018-10-21T02:11:42.837

1

I like this way by Kris Gielen (https://blog.krisgielen.be/archives/255):

UPDATE table SET categories =
  TRIM(BOTH ',' FROM REPLACE(CONCAT(',', categories, ','),
  CONCAT(',', '2', ','), ','))
WHERE ...

To add a value to the set:

UPDATE table SET categories = 
  CONCAT_WS(',', categories, '2')
WHERE ...

edited Oct 21 '18 at 02:11

answered Oct 21 '18 at 02:05

tim

2,530
3
26
45

The example to add a value doesn't work when the set is empty. – Per Aug 28 '22 at 20:57

Payel Senapati · Answer 7 · 2021-09-27T19:47:52.253

Here, the best answers have been provided by @palindrom and @JohnWoo. In fact the answer provided by @palindrom is exceptionally good and should be the accepted answer.

But, in case of answer provided by @JohnWoo, such a large case statement is absolutely unnecessary and can be done in a much more short and neat coding. I have illustrated this below -

UPDATE my_table
    -> SET categories = CASE
    -> WHEN categories LIKE "2"
    -> THEN REPLACE(categories,"2","")
    -> WHEN categories LIKE "2%"
    -> THEN REPLACE(categories,"2,","")
    -> WHEN categories LIKE "%2%"
    -> THEN REPLACE(categories,",2","")
    -> ELSE categories
    -> END;

Here, studying this post on combining two update statements is helpful. I have also provided an answer here.

bharat · Answer 8 · 2022-03-14T03:22:14.137

Try this to add or remove value(multiple occurrences) from your field, you may remove NULLIF & COALESCE if your field have default value as not null:

Mysql:
NULLIF(TRIM(BOTH ',' FROM REPLACE(CONCAT(REPLACE(CONCAT(',',REPLACE(COALESCE('20,21,22',''), ',', ',,'),','),',20,', ''),',30,'),',,', ',')),'') --- add 30 & remove 20


NULLIF(TRIM(BOTH ',' FROM REPLACE(CONCAT(REPLACE(CONCAT(',',REPLACE(COALESCE('20,21,22',''), ',', ',,'),','),'', ''),',30,'),',,', ',')),'') --- to just add 30


NULLIF(TRIM(BOTH ',' FROM REPLACE(CONCAT(REPLACE(CONCAT(',',REPLACE(COALESCE('20,20,21,22',''), ',', ',,'),','),',20,', ''),''),',,', ',')),'') --- to just remove 20

The best way to remove value from SET field?

8 Answers8

Linked