CODEWITHSUNDEEP
mysqlconcatenationmultiple-columns
whitehead1415
whitehead1415

Reputation: 435

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

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.

Upvotes: 0

Views: 423

Answers (1)

Diego Agulló
Diego Agulló

Reputation: 9576

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';

Upvotes: 6

Related Questions