INSERT ... ON DUPLICATE KEY UPDATE

At the moment, I'm preparing for the core certification exam. Although I thought that I knew almost everything about the new features in MySQL 4.1, I sometimes find some fantastic "goodies" while I'm learning for the exam. One of them is the INSERT ... ON DUPLICATE KEY UPDATE command.

This command is ideal for logging. You can insert into a table, but do an update, if the row (based on the primary key) already exists. An example might explain this best:

mysql> CREATE TABLE logdata (
    ->    id INT UNSIGNED NOT NULL,
    ->    count INT NOT NULL,
    ->    PRIMARY KEY (id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO logdata (id, count)
    ->    VALUES (1, 1)
    ->    ON DUPLICATE KEY UPDATE
    ->    count = count + 1;
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO logdata (id, count)
    ->    VALUES (2, 1)
    ->    ON DUPLICATE KEY UPDATE
    ->    count = count + 1;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO logdata (id, count)
    ->    VALUES (1, 1)
    ->    ON DUPLICATE KEY UPDATE
    ->    count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM logdata;
+----+-------+
| id | count |
+----+-------+
|  1 |     2 |
|  2 |     1 |
+----+-------+
2 rows in set (0.00 sec)

mysql>

First, the key values 1 and 2 have been inserted. Then we inserted the value 1 again, the command executed an UPDATE instead incrementing the count value to 2.