Working with Merge in Vertica
In this article we will talk about the Merge operation in HP Vertica.
What is Merge and why is important to know how to use it while working with Vertica ?
- to start with we all know that Vertica is optimized for query-intensive workloads and not for single insert or updates.
to speedup a bit the insert and update operations we can make use of the MERGE operation.
MERGE statement combines insert and update operations into a single operation.
Syntax for Merge
MERGE [/*+ direct */]
... INTO [[db-name.]schema.]target-table [ alias ]
... USING [[db-name.]schema.]source-table [ alias ]
... ON ( condition )
... [ WHEN MATCHED THEN UPDATE SET
column1 = value1 [, column2 = value2 ... ] ]
... [ WHEN NOT MATCHED THEN INSERT ( column1 [, column2 ...])
VALUES ( value1 [, value2 ... ] ) ]- you must have select permission on the source table and insert,update and delete on the target table.
- during the merge operation an exclusive lock will be required.
Examples of merge operations
- Table we will use for this demonstration
create table target_tbl (id int, name varchar(10));
insert into target_tbl values(1,'Adi');
insert into target_tbl values(2,'Adi');
insert into target_tbl values(3,'Adi');
commit;
create table source_tbl (id2 int , name2 varchar(10));
insert into source_tbl values(4,'Ju');
insert into source_tbl values(5,'Ju');
insert into source_tbl values(6,'Ju');
commit;- Merge Statement
MERGE INTO target_tbl
USING source_tbl b
ON id = b.id2
WHEN MATCHED THEN
UPDATE SET id = b.id2, name = b.name2
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (b.id2, b.name2);- Target Table Result Set before Merge
(dbadmin@:5433) [dbadmin] select * from target_tbl ;
id | name
----+------
1 | Adi
2 | Adi
3 | Adi- Executing the Merge Statement
- this is a simple merge statement that includes both MATCHED and NOT MATCHED options.
(dbadmin@:5433) [dbadmin] MERGE INTO target_tbl
USING source_tbl b
ON id = b.id2
WHEN MATCHED THEN
UPDATE SET id = b.id2, name = b.name2
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (b.id2, b.name2);
OUTPUT
--------
3
(1 row)
(dbadmin@:5433) [dbadmin] * select * from target_tbl;
id | name
----+------
1 | Adi
2 | Adi
3 | Adi
4 | Ju
5 | Ju
6 | Ju- WHEN MATCHED - an update statement will be executed
- WHEN NOT MATCHED - an insert statement will be executed (this is optional)
