sqlmysqlsql-update

UPDATE query to remove a whole specified number in a comma-delimited column

How to set particular value of column using update in mysql?

Following is my code:

update contact set admin_id = ' ' where admin_id like '%,519,%' and id='31'

enter image description here

I have a table having named contact and I have defined a column name admin_id as shown in the image. I have an id i.e. ,519,520,521 and now I want to delete only 519 from admin_id and want only ,520,521 inside the admin_id column.

What should be the query to achieve this?

Solution

  • This should work -

    update contact 
set admin_id = replace(admin_id, '519,', '') 
where admin_id like '%,519,%' 
and id='31'

    replace()