Open In App

Merge operation in HP Vertica

Improve
Improve
Like Article
Like
Save
Share
Report

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
Previous
Next
Share your thoughts in the comments
Similar Reads