MERGE Source Options
A MERGE
operation joins the target table to one of the following data sources:
- Another table
- View
- Subquery result set
Merging from Table and View Data
You merge data from one table into another as follows:
MERGE INTO target‑table USING { source‑table | source‑view } join-condition matching‑clause[ matching‑clause ]
If you specify a view, Vertica expands the view name to the query that it encapsulates, and uses the result set as the merge source data.
For example, the VMart table public.product_dimension
contains current and discontinued products. You can move all discontinued products into a separate table public.product_dimension_discontinued
, as follows:
=> CREATE TABLE public.product_dimension_discontinued ( product_key int, product_version int, sku_number char(32), category_description char(32), product_description varchar(128)); => MERGE INTO product_dimension_discontinued tgt USING product_dimension src ON tgt.product_key = src.product_key AND tgt.product_version = src.product_version WHEN NOT MATCHED AND src.discontinued_flag='1' THEN INSERT VALUES (src.product_key, src.product_version, src.sku_number, src.category_description, src.product_description); OUTPUT -------- 1186 (1 row)
Source table product_dimension
uses two columns, product_key
and product_version
, to identify unique products. The MERGE
statement joins the source and target tables on these columns in order to return single instances of non-matching rows. The WHEN NOT MATCHED
clause includes a filter (src.discontinued_flag='1'
), which reduces the result set to include only discontinued products. The remaining rows are inserted into target table product_dimension_discontinued
.
Merging from a Subquery Result Set
You can merge into a table the result set that is returned by a subquery, as follows:
MERGE INTO target‑table USING (subquery) sq-alias join-condition matching‑clause[ matching‑clause ]
For example, the VMart table public.product_dimension
is defined as follows (DDL truncated):
CREATE TABLE public.product_dimension ( product_key int NOT NULL, product_version int NOT NULL, product_description varchar(128), sku_number char(32), ... ) ALTER TABLE public.product_dimension ADD CONSTRAINT C_PRIMARY PRIMARY KEY (product_key, product_version) DISABLED;
Columns product_key
and product_version
comprise the table's primary key. You can modify this table so it contains a single column that concatenates the values of these two columns. This column can be used to uniquely identify each product, while also maintaining the original values from product_key
and product_version
.
You populate the new column with a MERGE
statement that queries the other two columns:
=> ALTER TABLE public.product_dimension ADD COLUMN product_ID numeric(8,2); ALTER TABLE => MERGE INTO product_dimension tgt USING (SELECT (product_key||'.0'||product_version)::numeric(8,2) AS pid, sku_number FROM product_dimension) src ON tgt.product_key||'.0'||product_version::numeric=src.pid WHEN MATCHED THEN UPDATE SET product_ID = src.pid; OUTPUT -------- 60000 (1 row)
The following query verifies that the new column values correspond to the values in product_key
and product_version
:
=> SELECT product_ID, product_key, product_version, product_description FROM product_dimension WHERE category_description = 'Medical' AND product_description ILIKE '%diabetes%' AND discontinued_flag = 1 ORDER BY product_ID; product_ID | product_key | product_version | product_description ------------+-------------+-----------------+----------------------------------------- 5836.02 | 5836 | 2 | Brand #17487 diabetes blood testing kit 14320.02 | 14320 | 2 | Brand #43046 diabetes blood testing kit 18881.01 | 18881 | 1 | Brand #56743 diabetes blood testing kit (3 rows)