Stupid but simple example: assume I have a table Item where I keep totals of the items that I receive. There are two columns: Item_Name (the primary key), and Items_In_Stock with the amount.

When I receive an item A in quantity X:

  • If the item does not exist, I insert a new record for Item A, and set the items in stock to X.
  • If there exists a record for Item A, 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 columns 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?

mysql performance upsert
Edit
Report