CODEWITHSUNDEEP
phpmysqlsqlmysqlisql-update
omkara
omkara

Reputation: 982

Mysql: Set particular value of column using update

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?

Upvotes: 2

Views: 146

Answers (3)

Simonluca Landi
Simonluca Landi

Reputation: 921

I guess something like this should work (but didn't test..)

UPDATE contact
SET admin_id = REPLACE(admin_id, '519', '')
WHERE admin_id like '%,519,%' and id='31'

you can find more here:

https://dev.mysql.com/doc/refman/5.7/en/replace.html

Upvotes: 3

ASR
ASR

Reputation: 1811

You can use MySQL REPLACE() to achieve this

UPDATE contact SET admin_id = REPLACE(admin_id, ',519,', ',') 
WHERE admin_id LIKE '%,519,%' AND id='31'

Upvotes: 3

Sougata Bose
Sougata Bose

Reputation: 31739

This should work -

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

replace()

Upvotes: 3

Related Questions