CODEWITHSUNDEEP
mysqlsqlsql-update
morandi3
morandi3

Reputation: 1115

The best way to remove value from SET field?

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?!

Upvotes: 25

Views: 16452

Answers (8)

bharat
bharat

Reputation: 1776

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

Upvotes: 0

Payel Senapati
Payel Senapati

Reputation: 1356

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.

Upvotes: 0

tim
tim

Reputation: 2722

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 ...

Upvotes: 2

Courtney Miles
Courtney Miles

Reputation: 4014

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.

Upvotes: 3

Laria Chabowski
Laria Chabowski

Reputation: 93

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.

Upvotes: 3

palindrom
palindrom

Reputation: 19121

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).

Upvotes: 12

John Woo
John Woo

Reputation: 263843

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)

Upvotes: 5

fthiella
fthiella

Reputation: 49089

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)

Upvotes: 34

Related Questions