MERGE — 基于源数据插入、更新表中的行
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source
ON join_condition
when_clause [...]
其中 data_source 是
{ source_table_name |
( source_query )
}
[ [ AS ] source_alias ]
而 when_clause 是
{ WHEN MATCHED THEN { merge_update } |
WHEN NOT MATCHED THEN { merge_insert }
}
而 merge_insert 是
INSERT [( column_name [, ...] )]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES
}
[ WHERE condition ]
而 merge_update 是
UPDATE SET { column_name = {
expression | DEFAULT } |
( column_name [, ...] ) = ( {
expression | DEFAULT } [, ...] )
} [, ...]
[ WHERE condition ]
[ DELETE WHERE condition ]
MERGE 命令通过使用 data_source，对
target_table_name 中的行进行修改。MERGE
提供了一个单一的 SQL 语句，可以有条件地 INSERT 或
UPDATE 行，这是一个通常需要多个过程式语言语句才能完成的任务。
首先，MERGE 命令从 data_source 到
target_table_name 执行联接，产生零个或多个候选更改行。
对于每个候选更改行，MATCHED 或 NOT MATCHED 的状态仅设置一次，
然后按指定顺序评估 WHEN 子句。如果其中一个被激活，则执行指定的操作。
对于任何候选更改行，最多只能激活一个 WHEN 子句。
MERGE 命令的操作与同名的常规 UPDATE、INSERT
命令具有相同的效果。这些命令的语法不同，特别是没有指定表名。所有操作都参考
target_table_name。
没有 MERGE 权限。如果指定了更新操作，则必须在 SET 子句中引用
target_table_name 的列的 UPDATE 权限，
如果指定了插入操作，则必须在 target_table_name 上拥有
INSERT 权限。权限在语句开始时进行测试，无论是否激活特定的 WHEN
子句，在随后的执行过程中都会进行检查。在引用 condition 中的
data_source 和 target_table_name
的任何列时，都需要具有 SELECT 权限。
如果 target_table_name 上定义了 RULES，则不支持 MERGE。
必须确保目标表中的一条记录不能与源表中的记录多次匹配，因为这可能导致目标表中更新的记录没有特定值。
target_table_name
要合并到的目标表的名称（可选带模式限定符）。
target_alias
目标表的替代名称。提供别名时，它完全隐藏了表的实际名称。例如，给定
MERGE foo AS f，MERGE 语句的其余部分必须将该表称为
f 而不是 foo。
source_table_name
源表、视图或转换表的名称（可选带模式限定符）。
source_query
一个查询（SELECT 语句或 VALUES 语句），提供要合并到
target_table_name 中的行。请参考
SELECT
语句或
VALUES
语句，了解其语法的说明。
source_alias
数据源的替代名称。提供别名时，它完全隐藏了指定了表还是查询。
join_condition
join_condition 是一个表达式，其结果为
boolean 类型的值（类似于 WHERE 子句），指定哪些
data_source 中的行与
target_table_name 中的行匹配。
只有尝试匹配 data_source 行的
target_table_name 列应出现在
join_condition 中。仅引用
target_table_name 列的
join_condition 子表达式只能影响采取哪些操作，
通常是以令人惊讶的方式。
when_clause
至少需要一个 WHEN 子句。
如果 WHEN 子句指定了 WHEN MATCHED，并且候选更改行与
target_table_name 中的一行匹配，
如果 condition 不存在或存在且评估为
true，则会激活 WHEN 子句。
如果 WHEN 子句指定了 WHEN NOT MATCHED，并且候选更改行与
target_table_name 中的一行不匹配，
如果 condition 不存在或存在且评估为
true，则会激活 WHEN 子句。
merge_insert
指定一个 INSERT 操作，将一行插入目标表中。
目标列名可以以任何顺序列出。如果根本没有给出列名列表，则默认为表中所有列按其声明的顺序。
未在显式或隐式列列表中出现的每个列将填充其声明的默认值或 null（如果没有默认值）。
如果任何列的表达式不是正确的数据类型，则会尝试进行自动类型转换。
如果 target_table_name 是分区表，则每行将路由到适当的分区并插入其中。
如果 target_table_name 是分区，则如果其中一个输入行违反分区约束，则会出现错误。
列名不能指定多次。 INSERT 操作不能包含子查询。
只能指定一个 VALUES 子句。
VALUES 子句只能引用来自源关系的列，因为根据定义，没有匹配的目标行。
merge_update
指定一个 UPDATE 操作，更新 target_table_name
的当前行。列名不能指定多次。
不要包含表名，因为这与 UPDATE 命令通常不同。
例如，UPDATE tab SET col = 1 是无效的。
自版本24.1.3之后，可以在UPDATE子句后面添加一个DELETE子句，DELETE子句语法如下：
DELETE WHERE 条件
条件 是一个用于过滤删除行的表达式。
请注意，如果设置了DELETE子句，target_table_name 被限制为只支持表类型 (例如, 视图不被支持), 而且只有此事务中匹配到的行受DELETE子句影响。
DELETE 只在oracle兼容模式下支持，如果目标表在merge into操作之前，有元组已经更新（包括删除，插入），此merge into操作不被允许。
column_name
target_table_name 中的列名。
如果需要，可以使用子字段名或数组下标限定列名。（仅插入复合列的某些字段将使其他字段为空。）
在引用列时，不要在目标列的规范中包含表名。
expression
赋给该列的表达式。该表达式可以使用该表中此列和其他列的旧值。
condition
返回类型为 boolean 的表达式。如果此表达式返回 true，则将激活 WHEN 子句，
并对该行执行相应的操作。该表达式可能不包含可能对数据库执行写操作的函数。
对于 WHEN MATCHED 子句的条件可以引用源关系和目标关系中的列。
对于 WHEN NOT MATCHED 子句的条件只能引用来自源关系的列，
因为根据定义，没有匹配的目标行。
成功完成后，MERGE 命令将返回一个命令标记，格式如下：
MERGE total-count
total-count 是更改的总行数（无论是插入、更新还是删除）。
如果 total-count 为 0，则没有任何行发生任何更改。
如果有 DELETE 子句, 命令标记为 DELETE , total-count 为总删除的行数。
基于新事务对 CustomerAccounts 进行维护。
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);
注意，由于执行过程中 MATCHED 结果不会改变，因此这与以下语句完全等效：
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;
尝试插入一个新的库存项以及库存数量。如果该项已经存在，则更新现有项的库存计数。不允许具有零库存的条目。
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;
wine_stock_changes 表可以是最近加载到数据库中的临时表，例如。
尝试通过 DELETE 子句删除行：
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')
;