How to Perform an UPSERT so that I can use both new and old values in update part

mysql performance upsert
WPFAbsoluteNewBie picture WPFAbsoluteNewBie · May 24, 2011 · Viewed 78.9k times · Source

Stupid but simple example: Assume I have a table 'Item' where I keeps totals of the items that receive.

Item_Name              Items_In_Stock

Item name is primary key here. How to i achieve the following when ever I receive item A in quantity X.

If the item does not exist, I insert a new recored for Item A and set the items in stock to X and if there exists a record where items in stock was Y then the new value in items in stock is (X + Y)

INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = 27 + (SELECT items_in_stock where item_name = 'A' )

My problem is that i have multiple column in my actual table. Is it a good idea to write multiple select statements in the update part?

Of course I can do it in code but is there a better way?

Answer

Michael J.V. picture Michael J.V. · May 24, 2011

As mentioned in my comment, you don't have to do the subselect to reference to the row that's causing ON DUPLICATE KEY to fire. So, in your example you can use the following:

INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = `new_items_count` + 27

Remember that most things are really simple, if you catch yourself overcomplicating something that should be simple then you are most likely doing it the wrong way :)

Related questions

MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More
MySQL vs MongoDB 1000 reads

I have been very excited about MongoDb and have been testing it lately. I had a table called posts in MySQL with about 20 million records indexed only on a field called 'id'. I wanted to compare speed with MongoDB and …

Read More