How Can I Efficiently Perform UPSERT Operations with Multiple Columns in SQL?

Performing UPSERT with Multiple Columns Using INSERT...ON DUPLICATE KEY UPDATE

To perform an UPSERT operation (insert new rows or update existing rows) while accommodating both new and existing values in the update part, the INSERT...ON DUPLICATE KEY UPDATE statement is commonly used. This technique allows you to handle different scenarios based on whether a record with the specified primary key exists or not.

Simple Example

Consider a table named Item with columns Item_Name (primary key) and Items_In_Stock containing inventory counts. When receiving an item, the following logic applies:

• If the item does not exist, insert a new record with the provided quantity.
• If the item exists, update the Items_In_Stock column by adding the new quantity to the existing value.

A naive approach would involve using multiple SELECT statements in the UPDATE part to retrieve the existing count, as in the following example:

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')

Improved Solution

However, this approach can be unnecessarily complex. PostgreSQL allows you to reference the values of the row that triggered the ON DUPLICATE KEY condition directly in the UPDATE clause. Therefore, you can simplify the statement as follows:

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

This optimized statement avoids the overhead of additional SELECT queries and keeps the code more concise and elegant.

The above is the detailed content of How Can I Efficiently Perform UPSERT Operations with Multiple Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!