The best way to remove value from SET field?

by

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)

More Related Contents:

  1. MySQL, update multiple tables with one query
  2. UPDATE multiple rows with different values in one query in MySQL
  3. Multiple Updates in MySQL
  4. mysql update column with value from another table
  5. update multiple rows using limit in mysql?
  6. MySQL/SQL: Update with correlated subquery from the updated table itself
  7. What are differences between INSERT and UPDATE in MySQL?
  8. MySql – Way to update portion of a string?
  9. MySQL UPDATE syntax with multiple tables using WHERE clause
  10. Update MySQL with if condition
  11. Update multiple rows with multiple ‘where’ clauses for each individual row
  12. MySQL update statement to store ranking positions
  13. Which DB technology is sued to solve the following search issue? [closed]
  14. MySQL pivot row into dynamic number of columns
  15. MySQL – Operand should contain 1 column(s)
  16. Restore table structure from frm and ibd files
  17. mysql pivot/crosstab query
  18. MySQL: Split comma separated list into multiple rows
  19. Most efficient way to get table row count
  20. UNION query with codeigniter’s active record pattern
  21. Remove Primary Key in MySQL
  22. Get most recent row for given ID
  23. Is merge statement available in MySQL
  24. Mysql covering vs composite vs column index
  25. MYSQL syntax not evaluating not equal to in presence of NULL
  26. MySQL: LAST_INSERT_ID() returns 0
  27. INSERT INTO or UPDATE with two conditions
  28. Calculating time difference between 2 dates in minutes
  29. Get total hours worked in a day mysql
  30. How do you force mysql LIKE to be case sensitive? [duplicate]

Leave a Comment