We talked about duplicating with MySQL earlier, and another MySQL tip worth pointing out is on inserting or updating in one query. The idea is that there will be times when you would like to update a record if it exists, or insert if it does not exist. This becomes especially true in the world of web 2.0 and API’s where the return value may come over a delay.

A real world example where this could come up is in payment processing. Let’s say we’re using the Paypal API to record a payment. The API will talk to us a few times. Immediately after the user submits the payment, the API tells us a transaction id, and that the status is pending. Then, later on, the API will talk to us again when the money has actually transferred and the payment is complete. From a query perspective, we will want to insert the first time, and update the second time.

The brute force approach to this problem is to do a read to see if the record exists, and update or insert accordingly:

$sql = 'SELECT TransId FROM Sales WHERE TransId = 123';
$rs = $db->query($sql);
if(mysql_num_rows($rs) == 1) {
    // do an SQL UPDATE
else {
    // do an SQL INSERT

And while this gets the job done, it would be nicer if we could work it all in to one query. To accomplish this, we can use the ON DUPLICATE KEY keyword.

INSERT INTO Sales(TransId, Status, Amount)
VALUES(123, 'Pending', 20)

If the transaction exists, this query will just change the Status from ‘Pending’ to ‘Paid’. Otherwise, it will insert a new record. And obviously, we can remove the hardcoded variables in the example, and replace them with dynamic ones returned from the API.

HTML Form Builder
Ryan Campbell

To Insert or To Update by Ryan Campbell

This entry was posted 3 years ago and was filed under Notebooks.
Comments are currently closed.


  1. Matt · 3 years ago

    Be forewarned, INSERT … ON DUPLICATE KEY is NOT replication-safe.

  2. Ryan Campbell · 3 years ago

    I believe that has been fixed. The last comment says it has, but I have also read a few blog posts where people claim that they have noticed a speed increase with replication using this technique. I haven’t tested myself though, so I can’t confirm.

  3. Alan Wallace · 3 years ago

    Love these short helpful posts. I’ve been doing this in the way of the first example, will be glad to remove some more cludge from my code.

  4. Rahul · 3 years ago

    Ever heard of REPLACE INTO?

  5. Harmen Janssen · 3 years ago

    A very helpful post, thanks for this little gem. This’ll certainly unclutter some of my code.

  6. Aron · 3 years ago

    For as far as I know, REPLACE INTO deletes the existing record and inserts a new one, when a duplicate key is found.

  7. Murtaza · 3 years ago

    A very helpful indeed. Thanks for posting Ryan

  8. Sridhar · 3 years ago

    i second Alan Wallace’s comment. Thanks for posting Ryan.

  9. Blomus · 3 years ago

    It is very useful. Thank you for posting Ryan

  10. Greg Jorgensen · 3 years ago

    MySQL has the non-standard ON DUPLICATE KEY and the (dangerous) REPLACE statement. If you are using some other RDBMS, or you want to steer clear of MySQL quirks, just try whatever is least likely to fail (INSERT or UPDATE) and if it fails do the other. Whether you do a SELECT first, try to INSERT a duplicate primary key, or try to UPDATE a non-existent row the RDBMS does exactly the same amount of work: it scans the primary key index to see if the key you are looking for is in the index.

    If you have a table that is frequently updated, just do the UPDATE, then check the number of affected rows. If zero rows were updated do the INSERT. Likewise if the rows in your table are usually not updated after they are inserted do the INSERT, and if it fails (check the number of rows again) do an UPDATE.

    You can also just DELETE before INSERTING if your UPDATE will affect all columns anyway. The DELETE won’t do anything if there’s no matching row. Remember to put the DELETE and INSERT inside a transaction.

    SQL operations don’t blow up or corrupt the database if they fail, and they don’t crash your application or throw and exception. Just look at the number of affected rows to see if your INSERT, UPDATE, or DELETE did anything or not.

  11. JayantB · 3 years ago

    I have following table


    ProductID ProductRangeID ProductName Desc StatusFlag

    1 1 Metal abc I

    2 2 Wood xyz I

    3 3 JDB pqr I

    4 1 OM XXX I

    5 2 Shiv KKK I

    I need to check Duplicate ProductName against same ProductRangeID before Inserting and Updating the values I want to Insert and update values in single procedure

    How I can Do it??? please help??