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;

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.