Syntax
------ 
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
 [ ON DUPLICATE KEY UPDATE
 col=expr
 [, col=expr] ... ]
 
Or:
 
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name [PARTITION (partition_list)]
 SET col={expr | DEFAULT}, ...
 [ ON DUPLICATE KEY UPDATE
 col=expr
 [, col=expr] ... ]
 
Or:
 
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 SELECT ...
 [ ON DUPLICATE KEY UPDATE
 col=expr
 [, col=expr] ... ]
 
Description
----------- 
INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL
extension to the INSERT statement that, if it finds a
duplicate unique or primary key, will instead perform an
UPDATE.
 
The row/s affected value is reported as 1 if a row is
inserted, and 2 if a row is updated, unless the API's
CLIENT_FOUND_ROWS flag is set.
 
If more than one unique index is matched, only the first is
updated. It is not recommended to use this statement on
tables with more than one unique index.
 
If the table has an AUTO_INCREMENT primary key and the
statement inserts or updates a row, the LAST_INSERT_ID()
function returns its AUTO_INCREMENT value.
 
The VALUES() function can only be used in a ON DUPLICATE KEY
UPDATE clause and has no meaning in any other context. It
returns the column values from the INSERT portion of the
statement. This function is particularly useful for
multi-rows inserts.
 
The IGNORE and DELAYED options are ignored when you use ON
DUPLICATE KEY UPDATE.
 
The PARTITION clause was introduced in MariaDB 10.0. See
Partition Pruning and Selection for details.
 
This statement activates INSERT and UPDATE triggers. See
Trigger Overview for details.
 
See also a similar statement, REPLACE.
 
Examples
-------- 
CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal
VARCHAR(30));
INSERT INTO ins_duplicate VALUES (1,'Aardvark'),
(2,'Cheetah'), (3,'Zebra');
 
If there is no existing key, the statement runs as a regular
INSERT:
 
INSERT INTO ins_duplicate VALUES (4,'Gorilla') ON
DUPLICATE KEY UPDATE animal='Gorilla';
Query OK, 1 row affected (0.07 sec)
 
SELECT * FROM ins_duplicate;
+----+----------+
| id | animal |
+----+----------+
| 1 | Aardvark |
| 2 | Cheetah |
| 3 | Zebra |
| 4 | Gorilla |
+----+----------+
 
A regular INSERT with a primary key value of 1 will fail,
due to the existing key:
 
INSERT INTO ins_duplicate VALUES (1,'Antelope');
ERROR 1062 (23000): Duplicate entry '1' for key
'PRIMARY'
 
However, we can use an INSERT ON DUPLICATE KEY UPDATE
instead:
 
INSERT INTO ins_duplicate VALUES (1,'Antelope') ON
DUPLICATE KEY UPDATE animal='Antelope';
Query OK, 2 rows affected (0.09 sec)
 
Note that there are two rows reported as affected, but this
refers only to the UPDATE.
 
SELECT * FROM ins_duplicate;
+----+----------+
| id | animal |
+----+----------+
| 1 | Antelope |
| 2 | Cheetah |
| 3 | Zebra |
| 4 | Gorilla |
+----+----------+
 
Adding a second unique column:
 
ALTER TABLE ins_duplicate ADD id2 INT;
UPDATE ins_duplicate SET id2=id+10;
ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);
 
Where two rows match the unique keys match, only the first
is updated. This can be unsafe and is not recommended unless
you are certain what you are doing. Note that the warning
shown below appears in MariaDB 5.5 and before, but has been
removed in MariaDB 10.0, as MariaDB now assumes that the
keys are checked in order, as shown in SHOW CREATE TABLE.
 
INSERT INTO ins_duplicate VALUES (2,'Lion',13) ON
DUPLICATE KEY UPDATE animal='Lion';
Query OK, 2 rows affected, 1 warning (0.06 sec)
 
SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1592 | Unsafe statement written to the binary log
using statement format since BINLOG_FORMAT = STATEMENT.
INSERT... ON DUPLICATE KEY UPDATE on a table with more than
one UNIQUE KEY is unsafe |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
SELECT * FROM ins_duplicate;
+----+----------+------+
| id | animal | id2 |
+----+----------+------+
| 1 | Antelope | 11 |
| 2 | Lion | 12 |
| 3 | Zebra | 13 |
| 4 | Gorilla | 14 |
+----+----------+------+
 
Although the third row with an id of 3 has an id2 of 13,
which also matched, it was not updated.
 
Changing id to an auto_increment field. If a new row is
added, the auto_increment is moved forward. If the row is
updated, it remains the same.
 
ALTER TABLE `ins_duplicate` CHANGE `id` `id` INT( 11 ) NOT
NULL AUTO_INCREMENT;
ALTER TABLE ins_duplicate DROP id2;
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
| 5 |
+----------------+
 
INSERT INTO ins_duplicate VALUES (2,'Leopard') ON
DUPLICATE KEY UPDATE animal='Leopard';
Query OK, 2 rows affected (0.00 sec)
 
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
| 5 |
+----------------+
 
INSERT INTO ins_duplicate VALUES (5,'Wild Dog') ON
DUPLICATE KEY UPDATE animal='Wild Dog';
Query OK, 1 row affected (0.09 sec)
 
SELECT * FROM ins_duplicate;
+----+----------+
| id | animal |
+----+----------+
| 1 | Antelope |
| 2 | Leopard |
| 3 | Zebra |
| 4 | Gorilla |
| 5 | Wild Dog |
+----+----------+
 
SELECT Auto_increment FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_NAME='ins_duplicate';
+----------------+
| Auto_increment |
+----------------+
| 6 |
+----------------+
 
Refering to column values from the INSERT portion of the
statement: 
 
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
 ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
 
See the VALUES() function for more.
 


URL:
https://mariadb.com/kb/en/insert-on-duplicate-key-update/