Sky Sky - 6 months ago 20
MySQL Question

mysql update REPLACE with concat

my table save same ID like that : 123,9901,888,99

I need change some id in this field

I using my sql : let 9901 change to 2001

UPDATE `TALBE_` SET id_group = REPLACE (id_group, '9901', '2001');


That's work

but, I change 99 to 100

UPDATE `TALBE_` SET id_group = REPLACE (id_group, '99', '100');


My sql fidle had changed to

123,10001,888,100

how to just change 99 -> 100, dont change 9901?

Do I need use concat?

But I test some time still cant do that

Answer

You can use e.g.

UPDATE `TALBE_` SET id_group = 
    trim(',' from REPLACE(concat(',', id_group, ','), ',99,', ',100,'));