The term Upsert in SQL allows you update a table if the specified condition already exists or insert a new row if the specified condition does not exist in the table.
That is, Upsert automatically allows you write a query to detect a record in a database if it exists or not by performing the functionality of either updating the table or inserting a new record into a table which in turn allows for efficiency in database operation.
The Upsert syntax varies based on the database system you use and in this article we will be exploring different methods in SQL to achieve this!
Now, let’s dive into an example.
Assuming you have a product table showing details of different products being sold in X company. This information includes id, name, category and price. You were asked to add a new product to the table but also want to confirm if it already exists so it doesn’t duplicate.
However, the condition to check if the product already exists is the name column. This is where the UPSERT function comes into play!
As a sales analyst, you want to add a new user with the product name Oven and Category Home Appliance. The purpose is to update the new price for the product which is “$2000”.
UPSERT IN POSTGRESQL
INSERT INTO product (id, name, category, price)
VALUES (104, 'Oven', 'Home appliance', '$2000')
ON CONFLICT (name) DO UPDATE SET
Price = '$2000'
This result updates the product Oven by replacing it with the price specified which is $2000 rather than inserting a new row because there is a conflict already on the name. This means the product name = Oven already exists.
The result looks like the table below:
Therefore, the Upsert operation replaced the product “Oven” because it already exists in the database.
UPSERT IN MYSQL
In this case, your query language will be a bit different however, performs the same function.
You can solve the same problem using ON DUPLICATE KEY UPDATE which allows you perform an UPSERT operation.
INSERT INTO product (id, name, category, price)
VALUES (104, 'Oven', 'Home Appliances', '$2000') ON DUPLICATE KEY UPDATE name = VALUES("name");
Instead of throwing an error whenever a record wants to be updated, it updates the record of the existing row to the new value specified just like the result in the first example.
Now, let’s take a look at another scenario where you are supposed to update the table with a product known as “Necklace”.
INSERT INTO product (id, name, category, price)
VALUES (104, 'Necklace', 'Clothing & Accessories', '$2000') ON DUPLICATE KEY UPDATE name = VALUES("name");
In this example, we are trying to update the Product table with the name “Necklace”. In this case, it inserts a new row with the specified id, name, category and price because there is no duplicated name knows as “Necklace”.
The query gives the below result.
UPSERT IN SQL SERVER
Another instance is the use of MERGE INTO function. For example we have a salesman table known as Salesman.
Layi Wasibi has now been transferred to Ogun because of his exceptional abilities to drive sales. You were asked to update the table to Layi Wasisbi’s current location.
Expected new record:
We can use the Upsert operation on the salesman table by updating Layi Wasibi’s location using the salesman_id column if it exists or not.
Below is the query to achieve this:
MERGE INTO salesman AS target
USING (VALUES (001, 'Layi Wasibi', 'Ogun', 'Power Oil')) AS source (salesman_id, salesman_name, salesman_location, salesman_product)
ON (target.salesman_id = source.salesman_id)
WHEN MATCHED THEN
UPDATE SET salesman_location = source.salesman_location
WHEN NOT MATCHED
INSERT (salesman_id, salesman_name, salesman_location, salesman_product)
VALUES (source.salesman.id, source.salesman_name, source.salesman_location, source.salesman_product);
In the above query, MERGE INTO combines the salesman table (target) with the expected new record (source) using the salesman_id column as seen in the result below.
‘WHEN MATCHED THEN’ states the result to be given when the salesman_id column from the target table = salesman_id from the source table. It updates the salesman_location with the value from the source table.
‘WHEN NOT MATCHED THEN’ states the result to be given when there is no match. Instead, it inserts a new row with the values from the source table.
The UPSERT function is a powerful tool that allows you update and insert tables.
