mysql on duplicate key update

Question

I am using mysql option to update result on duplicate. It works fine to update result. Now I was trying to concatenate existing row result with previous one and I used + and . concatenation operators but it is giving error.

Here is the pseudo code

insert in table 1
on duplicate key update prod_name = "abc" concatenate with existing result

so if there is record XYZ in the row then it should be XYZ, abc and on next duplicate it should be XYZ, abc, dpg and so on

Answer 1

use

$sql= " INSERT INTO table (prod_name) VALUES ($x)
  ON DUPLICATE KEY UPDATE prod_name='prod_name+$x' ";

Note: prod_name should be UNIQUE index or PRIMARY KEY

edit

try this

  $sql= " INSERT INTO table (prod_name) VALUES ($x) ";
  $sql .= is_numeric($x) 
   ? " ON DUPLICATE KEY UPDATE prod_name=prod_name+$x "
   : " ON DUPLICATE KEY UPDATE prod_name=CONCAT_WS(',',prod_name, $x) ";

Answer 2

I believe you're looking for the mysql function concat() (docs).

The SQL would look something like this:

INSERT INTO table (prod_name) VALUES ('abc')
  ON DUPLICATE KEY UPDATE prod_name=concat(prod_name,',','abc')