Examples of Upsert in SQL
Example 1: Upserting User Information
Step 1: Create the Users Table and Insert Initial Values
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO users (user_id, username, email)
VALUES
(1, 'rohit', 'rohit@gfg.com'),
(2, 'rohan', 'rohan@gfg.com');
Output:
Explanation: The SQL code creates a table named “users” with columns for user ID, username, and email. It then inserts two rows of user data. The output confirms the successful insertion of the provided user information.
Step 2: Perform Upsert Operation
INSERT INTO users (user_id, username, email)
VALUES
(3, 'sumit', 'sumit@gfg.com'),
(1, 'ayush', 'ayush@gfg.com')
ON DUPLICATE KEY UPDATE
username = VALUES(username),
email = VALUES(email);
Output:
Explanation: The SQL statement inserts new user data into the “users” table. If a duplicate key conflict arises, it updates the existing record’s username and email. The output confirms the successful insertion and potential updates.
Example 2: Upserting Product Prices
Step 1: Create the Users Table and Insert Initial Values
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
price DECIMAL(10, 2)
);
INSERT INTO products (product_id, product_name, price)
VALUES
(101, 'Laptop', 1200.00),
(102, 'Smartphone', 800.00);
Output:
Explanation: The SQL code creates a “products” table with columns for product ID, name, and price. It then inserts two rows of product data. The output confirms the successful creation and insertion of product information into the table.
Step 2: Perform Upsert Operation
INSERT INTO products (product_id, product_name, price)
VALUES
(103, 'Tablet', 500.00),
(102, 'Updated Smartphone', 850.00)
ON DUPLICATE KEY UPDATE
product_name = VALUES(product_name),
price = VALUES(price);
Output:
Explanation: The SQL code inserts new product data into the “products” table. If a conflict arises due to duplicate keys, it updates the existing entry with the provided values for product name and price. The output confirms the successful insertion of new data and updates to existing records, ensuring data integrity.
How to Write Upsert in SQL?
In SQL, the “upsert” operation is a combination of “insert” and “update” operations. It allows you to add new rows if they do not exist in the table, or to replace existing rows if they exist. The Upsert function is useful when you need to synchronize data between different sites, maintain data consistency, or manage collaboration efficiently.
In this article, we’ll explore the introduction of SQL’s “upsert” operation, which combines insertion and updating. We’ll cover its syntax and provide examples to illustrate its functionality in data management.