MySQL: Insert if doesn’t exist otherwise update the existing row

• inShare

A good practice of increasing SQL efficiency is to reduce the number of separate queries as much as possible. In a situation where you want a row updated if it exists and inserted if it doesn’t may take a newbie MySQL developer to write 2 independent queries, namely:

1. first, check if the row exists with “SELECT * FROM table WHERE …”
2. second, if it exists, “UPDATE table SET …”; ir it doesn’t exist, “INSERT INTO table …”

thus loading the server with unnecessary burdens. Checking if a certain record already exists and decide between insert or update can be simply achieved by a single MySQL query:

REPLACE INTO table SET id = 42, foo = 'bar';

REPLACE INTO first checks if the row with id = 42 already exists, if it does, it deletes the row and insert the current one; if it doesn’t, it simply inserts the current record.

Another way is to use ON DUPLICATE KEY UPDATE clause in a paradigm such as INSERT … ON DUPLICATE KEY UPDATE to determine if the record to be inserted can cause a duplicate entry in a UNIQUE index or PRIMARY KEY index, if it doesn’t, it inserts it, if it does, the existing row is updated. The following two statements have the same results:

INSERT INTO table (a, b, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1;
// column ‘a’ is UNIQUE indexed, thus to prevent duplicate UNIQUE column values, the old row (whose a = 1) is updated with a new value of ‘c’

UPDATE table SET c = c + 1 WHERE a = 1;