MySQL's `ON DUPLICATE KEY UPDATE`

MySQL offers a SQL extension in the ON DUPLICATE KEY UPDATE clause. This extends INSERT functionality such that if data already exists, identified by the key(s), then it is updated, while if it does not then it is inserted. Some care is required using this in practice.

From the MySQL documentation:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

Unsurprisingly, the UPDATE action is taken when insertion results in a collision in the primary key, but additionally, the UPDATE action is also taken when insertion results in a collision on a unique key.

Here is a small example of the latter. It's worth noting that this code results in Anikin transforming into Vader simply because (in my contrived example) their saber colours match. In this example, the UPDATE action being taken has nothing to do with the primary key.

DROP TABLE IF EXISTS `StrongInTheForce`;
CREATE TABLE `StrongInTheForce` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `Name` VARCHAR(255) NOT NULL,
    `Allegiance`  ENUM('light', 'dark') DEFAULT 'light',
    `SaberColour` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`ID`),
    UNIQUE INDEX (`SaberColour`)
) ENGINE=INNODB CHARSET=UTF8;

INSERT INTO StrongInTheForce (`Name`, `Allegiance`, `SaberColour`)
    VALUES ('Jedi Master Yoda', 'light', 'green'),
    ('Anikin Skywalker', 'light', 'blue');
ID Name Allegiance SaberColour
1 Jedi Master Yoda light green
2 Anikin Skywalker light blue
INSERT INTO StrongInTheForce (`Name`, `Allegiance`, `SaberColour`)
    VALUES ('Darth Vader', 'dark', 'blue')
    ON DUPLICATE KEY UPDATE
        `Name` = 'Darth Vader',
        `Allegiance` = 'dark';
ID Name Allegiance SaberColour
1 Jedi Master Yoda light green
2 Darth Vader dark blue

In practice you need to be very careful with your keying to use this extension with no side-effects. I have found, and fixed, bugs related to this in production code, and they're nasty. Unless you have a good reason I think it is better practice to separate the concerns: INSERT if the data is new, UPDATE if it is not, and determine which case you're dealing with via SELECT.