MERGE Statement with Filters

This blog post was authored by Soniya Shah.

Vertica 8.1 introduced new functionality for the MERGE statement. In this post, we discuss new functionality for MERGE that allows users to filter conditions on INSERT and UPDATE clauses in a MERGE statement.

The MERGE operation allows users to join the target table on another table, a view, or a subquery result set. The new syntax allows users to specify an UPDATE filter and an INSERT filter as follow. The MERGE will INSERT new rows, if an ID does not match, and the INSERT filter conditions are met or UPDATE rows if there is an ID match, the UPDATE filter conditions are met, and at least one column value is different. The syntax is efficient because there is better control of when you can update or insert.

The goal of this new syntax is to limit the number of rows affected by the MERGE statement on a target table. For example, if you are merging source table rows that differ from the matching destination table rows, only the rows with a change are updated. This saves time because identical rows are never updated.

The new syntax is depicted as follows: MERGE [ /*+ [, ] */ ] INTO [[.].] [ [AS] ] USING [[.].] [ [AS] ] ON [ ] = one of the following: WHEN MATCHED [ AND ] THEN UPDATE SET { = }[,...] WHEN NOT MATCHED [ AND ] THEN INSERT ( [,...] ) VALUES ( [,...] You might wonder when to use MERGE with filters and when to use MERGE with subqueries. Often, it depends on what you are looking to merge. To filter rows from a table using just its attributes, use MERGE with subqueries. Both options work in every scenario when you must filter a predicate from the table, such as merging all records after a certain timestamp.

Use this new syntax on wide tables or on tables with many projections. In both cases, you might want to skip identical rows, and use this syntax to save time and resources.

You should NOT use the new MERGE syntax in place of the optimized MERGE statement.

For more information, see Update and Insert Filters in the Vertica documentation and check out our previous post about how to use MERGE to demonstrate Vertica’s performance at scale here.