Categories
SQL / MySQL Tips and Tutorials

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

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;

By Yang Yang

Hello, I'm Yang. I build online businesses that please people. Want to join in and post some useful articles on Kavoir.com? Shoot me a message.

12 replies on “MySQL: Insert if doesn’t exist otherwise update the existing row”

Thanks for the article. It was exactly what I was looking for. You saved me a lot of time. I hope I can return the favor back to the community of developers soon. : )

Cheers for the article. Always find the MySQL official documentation a bit heavy to read and understand. Could understand this though 🙂

how can I check for more than one field?

I would link to insert the field in case that 3 of its columns don’t exist.

INSERT INTO TABLE (id,ip,date,rate) VALUES (NULL,’111.333.222.444′,now(),’3′)

However, I’m checking not only for the Primary Key (which is an auto-increment anyways), but for fields ‘ip’ and ‘date’.

I need for an IP to be able to rate once per day.

Thanks!

Jo, you can create one more filed, with a md5key with the date and the ip.

example on php:
$ip = $_SERVER[‘remote_addr’];
$today = date(“Y-m-d”);
$key = md5($ip.”:”.$today);

and the example:
REPLACE INTO table SET md5key= $key, foo = ‘bar’;

or

INSERT INTO table (a, b, c) VALUES ($key, 2, 3) ON DUPLICATE KEY UPDATE c = c + 1;

nice way to do it, but it doesn’t work when the id is present as a foreing key in another table. There’s an error of restriction or even worst, a cascade elimination of registers in the foreing table. Take care of this point before try this command. Bye!

Comments are closed.