2

I have a table namely all_products. In which tags are stored in the comma-separated form. Like tag1, tag2, tag3. Now if the user updates the tag then I need to update the tag value in this set also. This table is used for caching purpose so can not normalize it. What I've done is

UPDATE all_products SET tags = replace(tags,
         'Tag2', 'Tag2 Updated')
WHERE find_in_set('Tag2', tags)

The issue in the above query is if there are two tags namely tag2 and tag22 in the same row then the query will update both. I want it to update tag2 only.

Is there any way by which I can update the exact tag only?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Vidhyut Pandya
  • 1,605
  • 1
  • 14
  • 27

1 Answers1

1

Use the operator LIKE:

UPDATE all_products 
SET tags = trim(both ',' from replace(concat(',', tags, ','), ',Tag2,', ',Tag2 Updated,'))
WHERE concat(',', tags, ',') like '%,Tag2,%'

This code assumes that there are no spaces between the tags and the commas.
See the demo.

create table all_products(tags varchar(100)); 
insert into all_products(tags) values
('a,b,c'),                                       
('a,Tag2,c'),
('Tag2,b,Tag22');

Results:

| tags                 |
| -------------------- |
| a,b,c                |
| a,Tag2 Updated,c     |
| Tag2 Updated,b,Tag22 |

Edit
As of @Nick's recomendation, you can use find_in_set() in the WHERE clause:

WHERE find_in_set('Tag2', tags) > 0
forpas
  • 160,666
  • 10
  • 38
  • 76