MySQL on duplicate key update

Here’s an oft forgotten or overlooked MySQL feature: ON DUPLICATE KEY UPDATE. Used at the end of an insert statement, it’s a graceful way of updating data without having to check a record already exists.

SQL:

CREATE TABLE impressions(
ad_id int UNSIGNED NOT NULL PRIMARY KEY,
views int UNSIGNED NOT NULL);

INSERT INTO impressions (ad_id, views)
VALUES (2,1) ON DUPLICATE KEY UPDATE views=views+1;

As you can see, it’s perfect for keeping track of statistical data. Unlike MySQL’s REPLACE it doesn’t delete the row and run another insert, so it’s much safer. (it won’t activate ON DELETE triggers)