The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
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