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.
UPSERT INTO table_name [(column1, column2, ...)] VALUES (value1, value2, ...) [ , (value1, value2, ...) [, ...] ];
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.
In addition to
UPSERT...VALUES, Polypheny also supports
UPSERT keyword in SQL combines the functionality of
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;
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.
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
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.