Merge operation in HP Vertica
In HP Vertica the merge operation is a combination of the insert and the update operations.The condition specified is used for updating the values.
The source table can include new and existing data. If the target table does not include any of the source table’s records (new or existing), MERGE inserts all source data into the target. If the target table contains the row already then merge operation is used to update the values of the target table data with the source table data.
Syntax:
MERGE INTO schema_name.target_table
USING schema_name.source_table ON [condition]
WHEN MATCHED THEN UPDATE SET col1=val1...
WHEN NOT MATCHED THEN INSERT VALUES (source.col1, ..);
Example:
MERGE INTO target TGT
USING source SRC
ON SRC.A=TGT.A
WHEN MATCHED THEN
UPDATE SET A=TGT.A, B=TGT.B, C=TGT.C, D=TGT.D, E=TGT.E
WHEN NOT MATCHED THEN
INSERT VALUES (SRC.A, SRC.B, SRC.C, SRC.D, SRC.E);
Explanation:
The new rows “908 – KARAN – 99” and “777 – MANU – 67″ are inserted as new rows in the resultant table. But we see that the row ” 201 – TIM – 94″ is present in source and the target table ( on condition SOURCE.NUMBER = TARGET.NUMBER ), hence we update the values in the resultant table by taking the values from the source table.
Last Updated :
09 Sep, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...