CODEWITHSUNDEEP
mysql
Björn C
Björn C

Reputation: 4008

How to use `CONCAT` in a `UPDATE`

I´m trying to use CONCAT in a mysql UPDATE.

"INSERT INTO table ( 
   objekt_nr,
   objekt_status)
 VALUES(
  :objekt_nr,
  'salj,$fakt')

 ON DUPLICATE KEY UPDATE
  objekt_status = VALUES(CONCAT(objekt_status, 'salj,$fakt'))";

 $query_params = array(
   ':objekt_nr' => $_POST['objekt_nr']);

I have tried several:

objekt_status = VALUES(CONCAT(objekt_status, objekt_status))";
objekt_status = VALUES(CONCAT(objekt_status, 'addMe'))";
objekt_status = VALUES(CONCAT(objekt_status, 'salj,$fakt'))";
objekt_status = VALUES((CONCAT(objekt_status, 'salj,$fakt')))";

Error Code for: objekt_status = VALUES(CONCAT(objekt_status, 'salj,$fakt'))";
...syntax to use near '(objekt_status, 'salj,fakt,'))'

How should the code look like?

Upvotes: 1

Views: 419

Answers (3)

Björn C
Björn C

Reputation: 4008

Actually in my case i needed the "Values" for every line but the CONCAT line.

objekt_created_when = VALUES(objekt_created_when),
objekt_status = CONCAT(objekt_status, 'salj,$fakt') ";

If i did remove VALUES from all rows, values in db, got empty!

Upvotes: 0

A J
A J

Reputation: 4024

Try removing values as well as semicolon from the query

   "INSERT INTO table ( 
    objekt_nr,
    objekt_status)
    VALUES(
   :objekt_nr,
   'salj,$fakt')

    ON DUPLICATE KEY UPDATE
    objekt_nr = objekt_nr,
    objekt_status = CONCAT(objekt_status, 'salj,'$fakt)";

Upvotes: 1

Andy
Andy

Reputation: 17771

You have an semicolon where there should be a comma (after VALUES(objekt_nr);), and it appears the apostrophe is in the wrong place on the last line at $fakt. VALUES is only required for the INSERT, manual here

This query should be correct:

"INSERT INTO table ( 
   objekt_nr,
   objekt_status)
 VALUES(
  :objekt_nr,
  'salj,$fakt')

 ON DUPLICATE KEY UPDATE
  objekt_nr = objekt_nr,
  objekt_status = CONCAT(objekt_status, 'salj,$fakt')";

Also please ensure your variables are escaped, or use a prepared statement.

Upvotes: 3

Related Questions