MERGE
按条件将源表中的行合并到目标表中。通常这只能通过单独使用多个插入、更新或删除语句来实现。有关 MERGE 允许您合并的操作的更多信息,请参阅 UPDATE、DELETE 和 INSERT。
语法
MERGE INTO target_table USING source_table [ [ AS ] alias ] ON match_condition [ WHEN MATCHED THEN { UPDATE SET col_name = { expr } [,...] | DELETE } WHEN NOT MATCHED THEN INSERT [ ( col_name [,...] ) ] VALUES ( { expr } [, ...] ) | REMOVE DUPLICATES ]
参数
- target_table
-
MERGE 语句合并到的临时表或永久表。
- source_table
-
提供要合并到 target_table 的行的临时表或永久表。source_table 也可以是 Spectrum 表。
- alias
-
source_table 的临时备用名称。
此参数为可选的。在别名前加上 AS 也是可选的。
- match_condition
-
在源表列和目标表列之间指定同等的谓词,用于确定 source_table 中的行是否可以与 target_table 中的行匹配。如果满足条件,MERGE 会对该行运行 matched_clause。否则 MERGE 会为该行运行 not_matched_clause。
- WHEN MATCHED
-
指定当源行和目标行之间的匹配条件计算结果为 True 时要运行的操作。您可以指定 UPDATE 操作或 DELETE 操作。
- UPDATE
更新 target_table 中的匹配行。仅更新您在 col_name 中指定的值。
- 删除
删除 target_table 中的匹配行。
- WHEN NOT MATCHED
-
指定当匹配条件计算结果为 False 或 Unknown 时要运行的操作。只能为此子句指定 INSERT 插入操作。
- INSERT
根据 match_condition,从 source_table 插入与 target_table 中的任何行都不匹配的 target_table 行。可以按任意顺序列出目标 col_name。如果您不提供任何 col_name 值,则默认顺序是表中所有列的声明顺序。
- col_name
-
要修改的一个或多个列名。指定目标列时不包括表名。
- expr
-
定义新 col_name 值的表达式。
- REMOVE DUPLICATES
-
指定 MERGE 命令在简化模式下运行。简化模式具有以下要求:
target_table 和 source_table 必须具有相同的列数和兼容的列类型。
在 MERGE 命令中省略 WHEN 子句以及 UPDATE 和 INSERT 子句。
在 MERGE 命令中使用 REMOVE DUPLICATES 子句。
在简化模式下,MERGE 执行以下操作:
target_table 中与 source_table 中存在匹配项的行将更新为与 source_table 中的值相匹配。
source_table 中与 target_table 中不存在匹配项的行将插入到 target_table 中。
当 target_table 中的多个行与 source_table 中的同一行匹配时,将删除重复的行。Amazon Redshift 保留一行并对其进行更新。与 source_table 中的行不匹配的重复行将保持不变。
与使用 WHEN MATCHED 和 WHEN NOT MATCHED 相比,使用 REMOVE DUPLICATES 可获得更好的性能。如果 target_table 和 source_table 兼容,并且您不需要在 target_table 中保留重复行,我们建议您使用 REMOVE DUPLICATES。
使用说明
-
要运行 MERGE 语句,您必须是 source_table 和 target_table 的所有者,或者具有这些表的 SELECT 权限。此外,您必须拥有 target_table 的 UPDATE、DELETE 和 INSERT 权限,具体取决于 MERGE 语句中包括的操作。
-
target_table 不能是系统表、目录表或外部表。
-
source_table 和 target_table 不能是同一个表。
-
不能在 MERGE 语句中使用 WITH 子句。
-
source_table 中的行不能匹配 target_table 中的多行。
考虑以下示例:
CREATE TABLE target (id INT, name CHAR(10)); CREATE TABLE source (id INT, name CHAR(10)); INSERT INTO target VALUES (1, 'Bob'), (2, 'John'); INSERT INTO source VALUES (1, 'Tony'), (1, 'Alice'), (3, 'Bill'); MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET id = source.id, name = source.name WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name); ERROR: Found multiple matches to update the same tuple. MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name); ERROR: Found multiple matches to update the same tuple.
这两个 MERGE 语句中的操作均失败,因为
source
表中有多个行具有 ID 值1
。 -
match_condition 和 expr 不能部分引用 SUPER 类型列。例如,如果您的 SUPER 类型对象是数组或结构,则不能为 match_condition 或 expr 使用该列的个别元素,但您可以使用整列。
考虑以下示例:
CREATE TABLE IF NOT EXISTS target (key INT, value SUPER); CREATE TABLE IF NOT EXISTS source (key INT, value SUPER); INSERT INTO target VALUES (1, JSON_PARSE('{"key": 88}')); INSERT INTO source VALUES (1, ARRAY(1, 'John')), (2, ARRAY(2, 'Bill')); MERGE INTO target USING source ON target.key = source.key WHEN matched THEN UPDATE SET value = source.value[0] WHEN NOT matched THEN INSERT VALUES (source.key, source.value[0]); ERROR: Partial reference of SUPER column is not supported in MERGE statement.
有关 SUPER 类型的更多信息,请参阅 SUPER 类型。
-
如果 source_table 很大,则将 target_table 和 source_table 中的联接列定义为分配键可以提高性能。
-
要使用 REMOVE DUPLICATES 子句,您需要对 target_table 拥有 SELECT、INSERT 和 DELETE 权限。
-
source_table 可以是视图或子查询。以下是 MERGE 语句的示例,其中 source_table 是一个用于移除重复行的子查询。
MERGE INTO target USING (SELECT id, name FROM source GROUP BY 1, 2) as my_source ON target.id = my_source.id WHEN MATCHED THEN UPDATE SET id = my_source.id, name = my_source.name WHEN NOT MATCHED THEN INSERT VALUES (my_source.id, my_source.name);
示例
以下示例创建了两个表,然后对它们运行 MERGE 操作,更新目标表中的匹配行并插入不匹配的行。然后,它将另一个值插入源表并运行另一个 MERGE 操作,这次是删除匹配行并从源表插入新行。
首先创建并填充源表和目标表。
CREATE TABLE target (id INT, name CHAR(10)); CREATE TABLE source (id INT, name CHAR(10)); INSERT INTO target VALUES (101, 'Bob'), (102, 'John'), (103, 'Susan'); INSERT INTO source VALUES (102, 'Tony'), (103, 'Alice'), (104, 'Bill'); SELECT * FROM target; id | name -----+------------ 101 | Bob 102 | John 103 | Susan (3 rows) SELECT * FROM source; id | name -----+------------ 102 | Tony 103 | Alice 104 | Bill (3 rows)
接下来,将源表合并到目标表,用匹配行更新目标表,并插入源表中没有匹配的行。
MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET id = source.id, name = source.name WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name); SELECT * FROM target; id | name -----+------------ 101 | Bob 102 | Tony 103 | Alice 104 | Bill (4 rows)
请注意,ID 值为 102 和 103 的行已更新,以匹配目标表中的名称值。此外,在目标表中插入一个 ID 值为 104 且名称值为 Bill 的新行。
接下来,在源表中插入新行。
INSERT INTO source VALUES (105, 'David'); SELECT * FROM source; id | name -----+------------ 102 | Tony 103 | Alice 104 | Bill 105 | David (4 rows)
最后,运行合并操作,删除目标表中的匹配行,然后插入不匹配的行。
MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN DELETE WHEN NOT MATCHED THEN INSERT VALUES (source.id, source.name); SELECT * FROM target; id | name -----+------------ 101 | Bob 105 | David (2 rows)
从目标表中删除 ID 值为 102、103 和 104 的行,并在目标表中插入 ID 值为 105 且名称值为 David 的新行。
以下示例显示了使用 REMOVE DUPLICATES 子句的 MERGE 命令的简化语法。
CREATE TABLE target (id INT, name CHAR(10)); CREATE TABLE source (id INT, name CHAR(10)); INSERT INTO target VALUES (30, 'Tony'), (11, 'Alice'), (23, 'Bill'); INSERT INTO source VALUES (23, 'David'), (22, 'Clarence'); MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES; SELECT * FROM target; id | name ---+------------ 30 | Tony 11 | Alice 23 | David 22 | Clarence (4 rows)
以下示例显示了使用 REMOVE DUPLICATES 子句的 MERGE 命令的简化语法,如果重复行在 source_table 中有匹配的行,则从 target_table 中移除重复行。
CREATE TABLE target (id INT, name CHAR(10)); CREATE TABLE source (id INT, name CHAR(10)); INSERT INTO target VALUES (30, 'Tony'), (30, 'Daisy'), (11, 'Alice'), (23, 'Bill'), (23, 'Nikki'); INSERT INTO source VALUES (23, 'David'), (22, 'Clarence'); MERGE INTO target USING source ON target.id = source.id REMOVE DUPLICATES; SELECT * FROM target; id | name ---+------------ 30 | Tony 30 | Daisy 11 | Alice 23 | David 22 | Clarence (5 rows)
MERGE 运行后,target_table 中只有一行的 ID 值为 23。由于 source_table 中没有 ID 值为 30 的行,因此 ID 值为 30 的两个重复行仍保留在 target_table 中。