MERGE — insert, update rows of a table based upon source data
MERGE INTOtarget_table_name
[ [ AS ]target_alias
] USINGdata_source
ONjoin_condition
when_clause
[...] wheredata_source
is {source_table_name
| ( source_query ) } [ [ AS ]source_alias
] andwhen_clause
is { WHEN MATCHED THEN {merge_update
} | WHEN NOT MATCHED THEN {merge_insert
} } andmerge_insert
is INSERT [(column_name
[, ...] )] { VALUES ( {expression
| DEFAULT } [, ...] ) | DEFAULT VALUES } [ WHEREcondition
] andmerge_update
is UPDATE SET {column_name
= {expression
| DEFAULT } | (column_name
[, ...] ) = ( {expression
| DEFAULT } [, ...] ) } [, ...] [ WHEREcondition
]
MERGE
performs actions that modify rows in the
target_table_name
,
using the data_source
.
MERGE
provides a single SQL
statement that can conditionally INSERT
or
UPDATE
rows, a task
that would otherwise require multiple procedural language statements.
First, the MERGE
command performs a join
from data_source
to
target_table_name
producing zero or more candidate change rows. For each candidate change
row the status of MATCHED
or NOT MATCHED
is set
just once, after which WHEN
clauses are evaluated
in the order specified. If one of them is activated, the specified
action occurs. No more than one WHEN
clause can be
activated for any candidate change row.
MERGE
actions have the same effect as
regular UPDATE
, INSERT
commands of the
same names. The syntax of those commands is different, notably that there is no
tablename is specified. All actions refer to the
target_table_name
.
There is no MERGE privilege.
You must have the UPDATE
privilege on the column(s)
of the target_table_name
referred to in the SET
clause
if you specify an update action, the INSERT
privilege
on the target_table_name
if you specify an insert action.
Privileges are tested once at statement start and are checked
whether or not particular WHEN
clauses are activated
during the subsequent execution.
You will require the SELECT
privilege on the
data_source
and any column(s)
of the target_table_name
referred to in a condition
.
MERGE is not supported if the target_table_name
has
RULES
defined on it.
target_table_name
The name (optionally schema-qualified) of the target table to merge into.
target_alias
A substitute name for the target table. When an alias is
provided, it completely hides the actual name of the table. For
example, given MERGE foo AS f
, the remainder of the
MERGE
statement must refer to this table as
f
not foo
.
source_table_name
The name (optionally schema-qualified) of the source table, view or transition table.
source_query
A query (SELECT
statement or VALUES
statement) that supplies the rows to be merged into the
target_table_name
.
Refer to the
SELECT
statement or
VALUES
statement for a description of the syntax.
source_alias
A substitute name for the data source. When an alias is provided, it completely hides whether table or query was specified.
join_condition
join_condition
is
an expression resulting in a value of type
boolean
(similar to a WHERE
clause) that specifies which rows in the
data_source
match rows in the
target_table_name
.
Only columns from target_table_name
that attempt to match data_source
rows should appear in join_condition
.
join_condition
subexpressions that
only reference target_table_name
columns can only affect which action is taken, often in surprising ways.
when_clause
At least one WHEN
clause is required.
If the WHEN
clause specifies WHEN MATCHED
and the candidate change row matches a row in the
target_table_name
the WHEN
clause is activated if the
condition
is
absent or is present and evaluates to true
.
If the WHEN
clause specifies WHEN NOT MATCHED
and the candidate change row does not match a row in the
target_table_name
the WHEN
clause is activated if the
condition
is
absent or is present and evaluates to true
.
merge_insert
The specification of an INSERT
action that inserts
one row into the target table.
The target column names can be listed in any order. If no list of
column names is given at all, the default is all the columns of the
table in their declared order.
Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.
If the expression for any column is not of the correct data type, automatic type conversion will be attempted.
If target_table_name
is a partitioned table, each row is routed to the appropriate partition
and inserted into it.
If target_table_name
is a partition, an error will occur if one of the input rows violates
the partition constraint.
Column names may not be specified more than once.
INSERT
actions cannot contain sub-selects.
Only one VALUES
clause can be specified.
The VALUES
clause can only refer to columns from
the source relation, since by definition there is no matching target row.
merge_update
The specification of an UPDATE
action that updates
the current row of the target_table_name
.
Column names may not be specified more than once.
Do not include the table name, as you would normally do with an
UPDATE command.
For example, UPDATE tab SET col = 1
is invalid.
column_name
The name of a column in the target_table_name
. The
column name
can be qualified with a subfield name or array subscript, if
needed. (Inserting into only some fields of a composite
column leaves the other fields null.) When referencing a
column, do not include the table's name in the specification
of a target column.
DEFAULT VALUES
All columns will be filled with their default values.
expression
An expression to assign to the column. The expression can use the old values of this and other columns in the table.
DEFAULT
Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).
with_query
The WITH
clause allows you to specify one or more
subqueries that can be referenced by name in the MERGE
query.
condition
An expression that returns a value of type boolean
.
If this expression returns true
then the WHEN
clause will be activated and the corresponding action will occur for
that row. The expression may not contain functions that possibly performs
writes to the database.
A condition on a WHEN MATCHED
clause can refer to columns
in both the source and the target relation. A condition on a
WHEN NOT MATCHED
clause can only refer to columns from
the source relation, since by definition there is no matching target row.
On successful completion, a MERGE
command returns a command
tag of the form
MERGE total-count
The total-count
is the total
number of rows changed (whether inserted, updated, or deleted).
If total-count
is 0, no rows
were changed in any way.
Perform maintenance on CustomerAccounts based upon new Transactions.
MERGE CustomerAccount CA USING RecentTransactions T ON T.CustomerId = CA.CustomerId WHEN MATCHED THEN UPDATE SET Balance = Balance + TransactionValue WHEN NOT MATCHED THEN INSERT (CustomerId, Balance) VALUES (T.CustomerId, T.TransactionValue);
notice that this would be exactly equivalent to the following
statement because the MATCHED
result does not change
during execution
MERGE CustomerAccount CA USING (Select CustomerId, TransactionValue From RecentTransactions) AS T ON CA.CustomerId = T.CustomerId WHEN NOT MATCHED THEN INSERT (CustomerId, Balance) VALUES (T.CustomerId, T.TransactionValue) WHEN MATCHED THEN UPDATE SET Balance = Balance + TransactionValue;
Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. Don't allow entries that have zero stock.
MERGE INTO wines w USING wine_stock_changes s ON s.winename = w.winename WHEN NOT MATCHED THEN INSERT VALUES(s.winename, s.stock_delta) WHERE s.stock_delta > 0 WHEN MATCHED THEN UPDATE SET stock = w.stock + s.stock_delta WHERE w.stock + s.stock_delta > 0;
The wine_stock_changes table might be, for example, a temporary table recently loaded into the database.