INSERT ON DUPLICATE KEY UPDATE

Sintassi

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] nome_tab [(nome_col,...)]
    {VALUES | VALUE} ({espr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      nome_col=espr
        [, nome_col=espr] ... ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] nome_tab
    SET col_name={espr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      nome_col=espr
        [, nome_col=espr] ... ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] nome_tab [(nome_col,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      nome_col=espr
        [, nome_col=espr] ... ]

Spiegazione

INSERT ... ON DUPLICATE KEY UPDATE è un'estensione di MariaDB/MySQL all'istruzione INSERT e, se trova un valore duplicato in un indice univoco o nella chiave primaria, esegue una UPDATE.

Il numero di affected rows aumenta di 1 per ogni riga inserita e di 2 per ogni riga modificata.

Se più di un indice univoco ha dei duplicati, viene aggiornato solo il primo. Si raccomanda di non utilizzare questa istruzione su tabelle che hanno più indici univoci.

Si veda anche l'istruzione simile: REPLACE.

Esempi

CREATE TABLE ins_duplicate (id INT PRIMARY KEY, animal VARCHAR(30));
INSERT INTO ins_duplicate VALUES (1,'Aardvark'), (2,'Cheetah'), (3,'Zebra');

Se non vi è alcuna chiave, l'istruzione funziona come una normale 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  |
+----+----------+

Una normale INSERT con una chiave primaria che viene impostata a 1 fallirebbe, a causa della chiave esistente:

INSERT INTO ins_duplicate VALUES (1,'Antelope');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Tuttavia, è possibile usare INSERT ON DUPLICATE KEY UPDATE:

INSERT INTO ins_duplicate VALUES (1,'Antelope') ON DUPLICATE KEY UPDATE animal='Antelope';
Query OK, 2 rows affected (0.09 sec)

Si noti che le rows affected sono 2, perché è stata eseguita una UPDATE.

SELECT * FROM ins_duplicate;
+----+----------+
| id | animal   |
+----+----------+
|  1 | Antelope |
|  2 | Cheetah  |
|  3 | Zebra    |
|  4 | Gorilla  |
+----+----------+

Aggiungiamo una seconda colonna univoca:

ALTER TABLE ins_duplicate ADD id2 INT;
UPDATE ins_duplicate SET id2=id+10;
ALTER TABLE ins_duplicate ADD UNIQUE KEY(id2);

Se vi sono duplicati in due chiavi univoche, solo una di esse viene aggiornata - questo comportamento non è sicuro né raccomandato:

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 |
+----+----------+------+

Sebbene la terza riga con id 3 abbia id2 13, quindi duplicato, questo non viene aggiornato.

Modificare l'id di un campo auto_increment. Se viene aggiunta una nuova riga, l'auto_increment avanza. Se si modifica una riga, rimane uguale.

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 |
+----------------+