To Insert or To Update
Date : 2007 09 27 Category : Design & Usability
ParticletreeWe 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.
