Update and Insert Filters
Each WHEN MATCHED
and WHEN NOT MATCHED
clause in a MERGE
statement can optionally specify an update filter and insert filter, respectively:
WHEN MATCHED AND update-filter THEN UPDATE …
WHEN NOT MATCHED AND insert-filter THEN INSERT …
Vertica also supports Oracle syntax for specifying update and insert filters:
WHEN MATCHED THEN UPDATE SET column-updates WHERE update-filter
WHEN NOT MATCHED THEN INSERT column-values WHERE insert-filter
Each filter can specify multiple conditions. Vertica handles the filters as follows:
- An update filter is applied to the set of matching rows in the target table that are returned by the
MERGE
join. For each row where the update filter evaluates to true, Vertica updates the specified columns. - An insert filter is applied to the set of source table rows that are excluded from the
MERGE
join. For each row where the insert filter evaluates to true, Vertica adds a new row to the target table with the specified values.
For example, given the following data in tables t11
and t22
:
=> SELECT * from t11 ORDER BY pk; pk | col1 | col2 | SKIP_ME_FLAG ----+------+------+-------------- 1 | 2 | 3 | t 2 | 3 | 4 | t 3 | 4 | 5 | f 4 | | 6 | f 5 | 6 | 7 | t 6 | | 8 | f 7 | 8 | | t (7 rows) => SELECT * FROM t22 ORDER BY pk; pk | col1 | col2 ----+------+------ 1 | 2 | 4 2 | 4 | 8 3 | 6 | 4 | 8 | 16 (4 rows)
You can merge data from table t11
into table t22
with the following MERGE
statement, which includes update and insert filters:
=> MERGE INTO t22 USING t11 ON ( t11.pk=t22.pk ) WHEN MATCHED AND t11.SKIP_ME_FLAG=FALSE AND ( COALESCE (t22.col1<>t11.col1, (t22.col1 is null)<>(t11.col1 is null)) ) THEN UPDATE SET col1=t11.col1, col2=t11.col2 WHEN NOT MATCHED AND t11.SKIP_ME_FLAG=FALSE THEN INSERT (pk, col1, col2) VALUES (t11.pk, t11.col1, t11.col2); OUTPUT -------- 3 (1 row) => SELECT * FROM t22 ORDER BY pk; pk | col1 | col2 ----+------+------ 1 | 2 | 4 2 | 4 | 8 3 | 4 | 5 4 | | 6 6 | | 8 (5 rows)
Vertica uses the update and insert filters as follows:
- Evaluates all matching rows against the update filter conditions. Vertica updates each row where the following two conditions both evaluate to true:
- Source column
t11.SKIP_ME_FLAG
is set to false. - The
COALESCE
function evaluates to true.
- Source column
- Evaluates all non-matching rows in the source table against the insert filter. For each row where column
t11.SKIP_ME_FLAG
is set to false, Vertica inserts a new row in the target table.