On Duplicate Key Update - Multiple Columns

Question

When using insert... on duplicate key update, what is the syntax to update multiple columns?

INSERT INTO table1 (col1, col2, col3, col4) VALUES (’$val1’, ‘$val2’, ‘$val3’, ‘$val4’)
ON DUPLICATE KEY UPDATE col2=‘$val2’, col3=‘$val3’, col4=‘$val4’ // <-- not sure

Update: I am using this within PHP. Since this is a syntax question, it very relevant.

$result = mysql_query("INSERT INTO table1 (col1, col2, col3, col4) 
                         VALUES (’$val1’, ‘$val2’, ‘$val3’, ‘$val4’)
                         ON DUPLICATE KEY UPDATE (col2=‘$val2’, col3=‘$val3’, col4=‘$val4’)")

Again, not sure about this last part with the "Update".

Answer 1

Well, this is old. But of course you only need to provide a value once, there's no reason to add it a second time in the query (which comes in handy for multiple inserts, or prepared statements):

INSERT INTO table1
  (col1, col2, col3, col4)
VALUES
  ('val1', 'val2', 'val3', 'val4')
ON DUPLICATE KEY UPDATE
  col2=VALUES(col2),
  col3=VALUES(col3) [,...]

Which has as advantage it will still work for a multiple insert statement:

INSERT INTO table1
  (col1, col2, col3, col4)
VALUES
  ('val1', 'val2', 'val3', 'val4'),
  ('val5', 'val6', 'val7', 'val8'),
  ('val9', 'val10', 'val11', 'val12')
ON DUPLICATE KEY UPDATE
  col2=VALUES(col2),
  col3=VALUES(col3) [,...]

A modern PHP example:

$sql = "INSERT INTO table1 (col1, col2, col3, col4) VALUES (?,?,?,?)
        ON DUPLICATE KEY UPDATE
        col2=VALUES(col2),
        col3=VALUES(col3),
        col4=VALUES(col4)";
$db->prepare($sql)->execute([$val1, $val2, $val3, $val4]);

Answer 2

INSERT INTO table1
  (`col1`, `col2`, `col3`, `col4`)
VALUES
  ('val1', 'val2', 'val3', 'val4')
ON DUPLICATE KEY UPDATE
  `col2`='val2',
  `col3`='val3', [...]

I fixed your quotes and tickmarks.

Answer 3

INSERT INTO tb (name, date, stat1, stat2, stat3) 
VALUES (?, ?, ?, ?, ?) 
ON DUPLICATE KEY UPDATE stat1 = stat1 + VALUES(stat1),
                        stat2 = stat2 + VALUES(stat2), 
                        stat3 = stat3 + VALUES(stat3)

Answer 4

For the sake of clear syntax there's another syntax form;

INSERT INTO `table1` SET `id`=$id,
                         `col2`='$col2',
                         `col3`='$col3'[, ...]
 ON DUPLICATE KEY UPDATE `col2`='$col2',
                         `col4`='$col4'[, ...]

Example;

INSERT INTO customers SET cid=10,
                          createdon=NOW(),
                          createdby='user',
                          cname='Steve'
  ON DUPLICATE KEY UPDATE modifiedon=NOW(),
                          modifiedby='user',
                          cname='Steve';

If there does not exist a customer with ID=10 in database it will be created and columns cid, createdon, createdby, cname will be set. If it does exist, then it will be updated and columns modifiedon, modifiedbym, cname will be updated.

NOTE#1: IF you put for primary key cid=0 here, it will fire AUTO_INCREMENT (of course, if pk column is defined as AUTO_INCREMENT) and a record will be inserted!

NOTE#2: ON DUPLICATE KEY UPDATE makes update for existing PK ID record. But also it makes update if DUPLICATE is made on any UNIQUE KEY column. For example, if you defined that cname column is UNIQUE, then saving record with cname='Steve' that already exist will result in UPDATE of that record (not new INSERT). Take care about this because you may expect that DB returns error for UNIQUE KEY constraint violation which will not happened here.

Answer 5

Your query seems to be correct. Here is my example of this type of query:

INSERT INTO Stat (id, month, year, views, redirects, onList, onMap, emails) VALUE ("' . $Id . '","' . $month . '","' . $year . '",0,0,"' . $data['onList'] . '","' . $data['onMap'] . '",0) ON DUPLICATE KEY UPDATE onList=onList+' . $data['onList'] . ', onMap=onMap+' . $data['onMap']