MERGE INTO and update when matched and when not matched

I am attempting to update records in a Databricks table via MERGE INTO function. I have it working perfectly when the keys match. However, I am also looking to update records on the target table when keys don’t match.

Here is the scenario:

with source_table as (select * from table1)

MERGE INTO
target_table tt
USING source_table
ON tt.key = source_table.key
WHEN MATCHED
THEN UPDATE
SET
tt.field_1 = source_table.field_a
tt.field_2 = source_table.field_b
WHEN NOT MATCHED
THEN UPDATE
SET
tt.field_1 = ‘text_value’
tt.field_2 = ‘text_value’

This works when I only use the "WHEN MATCHED" condition. However, the "WHEN NOT MATCHED" returns parse error (mismatched input ‘UPDATE’ expecting ‘INSERT’). I do not want to insert any new records into the target or source table, just looking to update fields within based on the matching of the keys.

Thank you in advance for any assistance!!

I have tried:
WHEN NOT MATCHED BY source_table
THEN UPDATE
SET
tt.field_1 = ‘text_value’
tt.field_2 = ‘text_value’

…and this:
WHEN NOT MATCHED BY tt
THEN UPDATE
SET
tt.field_1 = ‘text_value’
tt.field_2 = ‘text_value’

But these result in parse error (mismatched input ‘BY’ expecting {‘AND’, ‘THEN’}).

You need to sign in to view this answers