I am trying to perform merge on delta table (0.7.0 version) performing inserts, updates & deletes and I have a scenario in Merge statement where for update clause to consider all the columns except one column.
Say, I am update the records in the table which captures the crt_ts and upd_ts. crt_ts is only populated for the new records and during updates we should not update this column.
MERGE INTO Target_table tgt
USING Source_table src
ON src.pk_col = tgt.pk_col
WHEN MATCHED AND src.operation=="DELETE" THEN DELETE
WHEN MATCHED AND src.operation=="UPDATE" THEN UPDATE SET * -- except crt_ts column
WHEN NOT MATCHED AND src.operation!="DELETE" THEN INSERT *
I was thinking of joining with the target table and get the crt_ts for already existing records and perform a coalesce it with current_timestamp to pass value to crt_ts. If the target table is huge this join will become a bottle neck.
Other approach is specify the individual columns in the SET clause for UPDATE, this looks tedious when we have tables with more columns.
Any other possible ways to perform without performing the join with the target table and writing a huge SET clause?