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