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.
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, ..);
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);
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.