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

12 Comments / SQL / MySQL Tips and Tutorials / By /

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;

Related Posts

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

  1. Paul

    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. : )

  2. LED TV Tom

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

  3. Jon

    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!

  5. To Jon

    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;

  8. Pingback: MySQL: Update Multiple Rows or Records with One Single Query

  10. gustavo

    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.

Scroll to Top