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.

Upsert in SQL

Upsert, a combination of "update" and "insert," is a database operation that inserts a new record into a table if it doesn't exist, or updates an existing record if it does. It simplifies data maintenance by ensuring seamless insertion or modification of records based on a specified condition, enhancing data integrity and efficiency.

Syntax:

SQL syntax for advanced operations may vary depending on the database system. Commonly used syntax includes:

1. Using MERGE statement:

MERGE INTO target_table AS target
USING source_table AS source
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET target.column1 = source.new_value1, 
               target.column2 = source.new_value2
WHEN NOT MATCHED THEN
    INSERT (id, column1, column2)
    VALUES (source.id, source.new_value1, source.new_value2);

2 . Using INSERT ON DUPLICATE KEY UPDATE (MySQL):

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2;

3. Using INSERT ON CONFLICT DO UPDATE (PostgreSQL):

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (column1)
DO UPDATE SET column2 = EXCLUDED.column2;

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', '[email protected]'),
    (2, 'rohan', '[email protected]');

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', '[email protected]'),
    (1, 'ayush', '[email protected]')
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.

Benefits of Upsert in SQL

Upsert in SQL is a combination of "insert" and "update" operations, enabling:

• Efficiency: Reduces the need for conditional checks before inserting or updating data.
  
• Simplicity: Streamlines data management by handling both insertion and updating in a single operation.
  
• Data Integrity: Helps maintain consistency by ensuring existing data is updated or new data is inserted appropriately.
  
• Concurrency: Facilitates concurrent access to data, minimizing conflicts in multi-user environments.

Conclusion

In conclusion, the upsert operation in SQL seamlessly combines insertion and updating, ensuring data consistency and efficiency. It allows for the insertion of new records or updates to existing ones based on specified conditions. By simplifying data management, upserts facilitate tasks such as synchronizing data across multiple sites and maintaining collaboration. This operation enhances data integrity and streamlines database operations for improved performance.