How to use concat() to append to multiple columns in a single mysql update

Question

I can't figure out the syntax for Mysql update with multiple concatinations. I want to be able to append a string to the end of the string stored in the database but do it to multiple columns all at once. I can do one column at a time just fine with this

UPDATE `table1`.`column1` SET `category1` = CONCAT(category1,'$value[0]',) WHERE `id`='$id';

But when I try to do it to multiple columns in the same table I get a syntax error.

UPDATE `table1`.`column1`
SET `category1` = CONCAT(category1,'5'),
    `category2` = CONCAT(category2,'5'),
    `category3` = CONCAT(category3,'5'),
    `category4` = CONCAT(category4,'5'),
    `category5` = CONCAT(category5,'5'),
    `comments` = CONCAT(comments, 'jfsaklfsad')
WHERE `for_student_id`='46';

"You have an error in your SQL syntax;"

I can't find the syntax for separating each concat.

perhaps it's only "UPDATE table1 SET ..." -- otherwise show us your table structure — mdo
– mdo, Commented Jul 19, 2012 at 20:54
If we assume table1 is your table, then what is column1? — gen_Eric
– gen_Eric, Commented Jul 19, 2012 at 20:58
You qualify a table_name with a schema (database name), UPDATE mydatabase.table1 SET — spencer7593
– spencer7593, Commented Jul 19, 2012 at 21:54

Diego Agulló · Accepted Answer · 2012-07-19 20:57:11Z

6

According to MySQL docs, UPDATE does not support such syntax. You must reference the table name, without the column, before the SET:

UPDATE `table1`
SET `category1` = CONCAT(category1,'5'),
    `category2` = CONCAT(category2,'5'),
    `category3` = CONCAT(category3,'5'),
    `category4` = CONCAT(category4,'5'),
    `category5` = CONCAT(category5,'5'),
    `comments`  = CONCAT(comments, 'jfsaklfsad')
WHERE `for_student_id`='46';

answered Jul 19, 2012 at 20:57

Diego Agulló

9,6263 gold badges31 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use concat() to append to multiple columns in a single mysql update

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related