How to update a row without delete current value in mysql?

Question

One of my table has a field tags and the value of the field like {2,3,8}

tag_id  | r_tags  
--------|------------   
1       | {2,3,8}  
--------|------------  
2       | {5,8}

I want to update the field without deleting the current value. For ex. If I need to add 5 for tag id 1, then {2,3,8} should be like {2,3,8,5}

Now its works like

 1. Select the current value.
 2. Add 5 using php and created new string.
 3. Update the row.

I just want to know is it possible only through MySql?

@Jenz now i create the string using PHP, then update the value using simple update query like update table set r_tags='{2,3,8,5}' where tag_id='1'. — Nevin Thomas, Feb 05 '14 at 04:44
UPDATE your_table SET your_field = REPLACE(your_field, '}', concat(',', new_value, '}') WHERE your_field = ? — Raghunandan Krishnamurthy, Feb 05 '14 at 04:53
http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field?rq=1 — Surabhil Sergy, Feb 05 '14 at 04:56

score 3 · Accepted Answer · edited Feb 05 '14 at 05:12

This should work - Assuming you want to add 5 in r_tags in tag_id = 1, this should be the query -

UPDATE tags SET r_tags = CONCAT(SUBSTRING(r_tags, 1, CHAR_LENGTH(r_tags) - 1),',5','}') WHERE tag_id = 1;

In php,

// $new_val and $tag_id as variables.
$query = "UPDATE tags SET r_tags = CONCAT(SUBSTRING(r_tags, 1, CHAR_LENGTH(r_tags) - 1),',$new_val','}') WHERE tag_id = $tag_id"

score 0 · Answer 2 · answered Feb 05 '14 at 04:54

0

UPDATE your_table SET your_field = REPLACE(your_field, '}', concat(',', new_value, '}') WHERE your_field = ?

answered Feb 05 '14 at 04:54

Raghunandan Krishnamurthy

675
5
11

score 0 · Answer 3 · answered Feb 05 '14 at 05:03

0

You can try below query for Mysql and you can replace any value with '5' whatever you want to add.

Update tbl_tags SET r_tags = INSERT(r_tags, INSTR(r_tags, '}'), 1, CONCAT(',','5','}')) WHERE tag_id = '1'

answered Feb 05 '14 at 05:03

Jignesh Patel

1,028
6
10

How to update a row without delete current value in mysql?

3 Answers3