The UPSERT statement is used to insert new records or update existing ones into a table depending on whether a given condition (usually a unique constraint) is met.
Syntax
UPSERT INTO table_name [(column1, column2, ...)]
VALUES (value1, value2, ...) [ , (value1, value2, ...) [, ...] ];
Parameters:
table_name: Name of the table where you want to insert or update records.column1, column2, ...: Optional list of column names into which you want to insert data. If you omit this, you must provide values for all columns in the table in the correct order.value1, value2, ...: Values that correspond to the columns that you are inserting or updating data into. It is possible to provide sets of values to be inserted within one query.query: A SELECT query that provides the data for the insertion or update.
If a row with a matching key is found in the table, it will be updated with the given values, and if it doesn’t exist, it will be inserted.
Upserting Data from Another Table (UPSERT…SELECT)
In addition to UPSERT...VALUES, Polypheny also supports UPSERT...SELECT. The UPSERT keyword in SQL combines the functionality of INSERT and UPDATE. It inserts rows that don’t exist and updates the rows that do exist.
The syntax is as follows:
UPSERT INTO target_table [(column1, column2, ...)]
SELECT column1, column2, ...
FROM source_table
WHERE condition;
Using UPSERT...SELECT, if a row with a matching key is found in the target table, it will be updated with the values from the source table, and if it doesn’t exist, it will be inserted.
Examples
The UPSERT statement can be used like this:
UPSERT INTO employees (id, first_name, last_name, salary)
VALUES (3, 'John', 'Doe', 50000);
This statement will insert a new record for an employee with id 3, or if this employee already exists, it will update the existing record.
You can also use the UPSERT statement to insert or update multiple records at once:
UPSERT INTO employees (id, first_name, last_name, salary)
VALUES
(1, 'John', 'Doe', 60000),
(2, 'Jane', 'Smith', 70000),
(3, 'Bob', 'Johnson', 80000);
This statement will insert new records for employees with id 1, 2, and 3, or if these employees already exist, it will update their records.
Upserting data from another table can be done as follows:
UPSERT INTO target_table (column1, column2)
SELECT column1, column2
FROM source_table
WHERE condition;
This statement will insert rows from source_table into target_table that meet the WHERE condition, or if rows with matching keys already exist in target_table, those rows will be updated with the new values.
