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)
ON DUPLICATE KEY UPDATE Status = 'Paid'
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.
Be forewarned, INSERT … ON DUPLICATE KEY is NOT replication-safe.
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.
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.
Ever heard of REPLACE INTO?
A very helpful post, thanks for this little gem. This’ll certainly unclutter some of my code.
For as far as I know, REPLACE INTO deletes the existing record and inserts a new one, when a duplicate key is found.
A very helpful indeed. Thanks for posting Ryan
i second Alan Wallace’s comment. Thanks for posting Ryan.
It is very useful. Thank you for posting Ryan
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.
I have following table
Product
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??