Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

MERGE doesn't allow you to have different sets of columns for comparing a match and for updating

I need to merge new/updated records from a temp table into a staging table. Each row has a DateTime column that signals when the row was copied. I need MERGE to compare all non-primarykey column values (excluding the DateTime column) and only update the staging table if those non-primarykey column values are different. When the update happens though, the DateTime column value should also be updated. I can do this with an UPDATE and INSERT but they are probably not as efficient as MERGE. This is an example: UPDATE STAGINGTABLE SET DEST.NONPKCOL1 = TEMP.NONPKCOL1, DEST.DATETIMECOL = TEMP.DATETIMECOL -- DATETIMECOL is updated if an update is required FROM TEMPTABLE AS TEMP, STAGINGTABLE AS DEST WHERE TEMP.PKCOL = DEST.PKCOL AND (DEST.NONPKCOL1 <> TEMP.NONPKCOL1) -- DATETIMECOL is not used in the comparison

Comments

  • Hi Frank, thanks for reporting your problem. We'll let you know if there is an efficient way to do this using the MERGE function.
  • Thanks Danielle!
  • MERGE INTO STAGINGTABLE AS DEST USING TEMPTABLE AS TEMP ON TEMP.PKCOL = DEST.PKCOL AND (DEST.NONPKCOL1 <> TEMP.NONPKCOL1) WHEN MATCHED THEN UPDATE SET NONPKCOL1 = TEMP.NONPKCOL1, DATETIMECOL = TEMP.DATETIMECOL WHEN NOT MATCHED THEN INSERT (....) VALUES (...);

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.