2

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?

user3382203
  • 169
  • 1
  • 6
  • 25
omkara
  • 974
  • 5
  • 24
  • 50

3 Answers3

3

This should work -

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

replace()

Sougata Bose
  • 31,517
  • 8
  • 49
  • 87
  • how to use this query in codeigniter model @Sougata Bose $re = replace(admin_id,'$client_id,',''); $data = array( 'admin_id'=>$re ); $where = "admin_id like '%$client_id%' and id = '$id'"; $this->db->where($where); $this->db->update('contact',$data); – omkara Aug 01 '17 at 07:11
  • Not sure. But there must be some way to implement raw expression – Sougata Bose Aug 01 '17 at 07:12
  • okk but when I run this query it show me error and i.e. Call to undefined function replace() – omkara Aug 01 '17 at 07:14
  • THis should help - https://stackoverflow.com/questions/16435390/how-to-execute-my-sql-query-in-codeigniter – Sougata Bose Aug 01 '17 at 07:16
3

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'
ASR
  • 1,801
  • 5
  • 25
  • 33
3

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

Simonluca Landi
  • 931
  • 8
  • 21