Skip to content
Related Articles
Open in App
Not now

Related Articles

Merge operation in HP Vertica

Improve Article
Save Article
  • Last Updated : 09 Sep, 2021
Improve Article
Save Article

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. 

 

My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!