MERGE — insert, update rows of a table based upon source data
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source
ON join_condition
when_clause [...]
where data_source is
{ source_table_name |
( source_query )
}
[ [ AS ] source_alias ]
and when_clause is
{ WHEN MATCHED THEN { merge_update } |
WHEN NOT MATCHED THEN { merge_insert }
}
and merge_insert is
INSERT [( column_name [, ...] )]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES
}
[ WHERE condition ]
and merge_update is
UPDATE SET { column_name = {
expression | DEFAULT } |
( column_name [, ...] ) = ( {
expression | DEFAULT } [, ...] )
} [, ...]
[ WHERE condition ]
[ DELETE WHERE condition ]
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.
It is necessary to ensure that a record in the target table does not match multiple times with a record in the source table, as this may result in updated records in the target table not having a certain value.
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.
It is possible to specify a DELETE clause right after the UPDATE clause since release 24.1.3, the syntax for DELETE clause is as follows:
DELETE WHERE condition
condition is a expression used to filter out the rows.
Please note that target_table_name is restricted to table type only (for example, view is not supported) if DELETE clause is specified, and only the matched rows related to this transaction would be affacted by the DELETE clause.
The DELETE is supported only in oracle compatible mode, and if the target table's tuple has been updated （including delete and insert） before a merge into operation which has a delete clause , the merge into operation is not allowed.
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.
expression
An expression to assign to the column. The expression can use the old values of this and other columns in the table.
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.
If we have DELETE clause, the command tag would be DELETE and the total-count would be the deleted rows in total.
Perform maintenance on CustomerAccounts based upon new Transactions.
MERGE INTO 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 INTO 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.
Attempt to delete rows by DELETE clause:
create table tl( a char(10), b char(10), c char(10),d int, e char(10), f char(10) );
insert into tl(a,b,c,d,e,f) values('tl','b1','c1',1,'e1','f1_tl');
insert into tl(a,b,c,d,e,f) values('tl','b2','c2',2,'e2','f2_tl');
insert into tl(a,b,c,d,e,f) values('tl','b3','c3',3,'e3','f3_tl');
insert into tl(a,b,c,d,e,f) values('tl','b4','c4',4,'e4','f4_tl');
create table tr( a char(10), b char(10), c char(10),d int, e char(10), f char(10) );
insert into tr(a,b,c,d,e,f) values('tr','b1','c1',1,'e1','f1_tr');
insert into tr(a,b,c,d,e,f) values('tr','b2','c2',2,'e2','f2_tr');
insert into tr(a,b,c,d,e,f) values('tr','b33','c3',4,'e4','f3_tr');
merge into tl using tr
on (tl.b=tr.b)
when matched then
update set tl.d=tl.d+1
delete where tr.b='b1'
when not matched then
insert values('tl_i','b3','c3',4,'e4_i','f1_i')
;