MySQL, how update string field by concatenating to it?

Question

Question is how to update string field by concatenating to it?

Here is code for creating database and adding one row:

CREATE DATABASE  my_db;                                                          
USE my_db;

CREATE TABLE IF NOT EXISTS my_table(
     article_id INTEGER unsigned,
     revision_ids VARCHAR(10),
     PRIMARY KEY (article_id)
);

INSERT INTO my_table (article_id, revision_ids) VALUES (1, "154");

I need to write code which concatenate string to revision_ids field. For example, I need to concatenate ", 999" to "154", so I will get "154, 999". My version of code does not work:

UPDATE my_table SET revision_ids = CONCAT((SELECT revision_ids FROM my_table WHERE article_id = 1), ", ", "999") WHERE article_id = 1;

How to do it?

There is one important condition. Theoretically this concatenation could do several scripts, so it is important that while our updating nobody can change the field value.

You don't need the entire query. You just use `revision_ids` with whatever you want to `CONCAT` to it. — Kermit, Sep 18 '12 at 02:18
I won't post the answer (already there 3x) but here is the SQL fiddle anyway. http://sqlfiddle.com/#!2/8490d/1 — ApplePie, Sep 18 '12 at 02:24

score 6 · Accepted Answer · answered Sep 18 '12 at 02:19

6

This should be all you need:

UPDATE my_table SET revision_ids = CONCAT(revision_ids, ", ", "999") WHERE article_id = 1;

answered Sep 18 '12 at 02:19

Kermit

33,827
13
85
121

score 2 · Answer 2 · answered Sep 18 '12 at 02:18

2

I gues you can do this directly,

UPDATE my_table 
SET revision_ids = CONCAT(revision_ids, ', ', '999') 
WHERE article_id = 1;

answered Sep 18 '12 at 02:18

John Woo

258,903
69
498
492

score 2 · Answer 3 · answered Sep 18 '12 at 02:20

2

Use:

UPDATE my_table SET revision_ids = CONCAT(revision_ids, ", 999") WHERE article_id = 1;

answered Sep 18 '12 at 02:20

xdazz

158,678
38
247
274

MySQL, how update string field by concatenating to it?

3 Answers3

Linked