Description

The MERGE INTO statement is used to update records in the target table based on values from the source table or subquery. This feature can be used to synchronize data in the target table when the source table contains new rows (to be inserted), modified rows (to be updated), and deleted rows (to be deleted).

Syntax

MERGE INTO target_table 
   USING source_table
   ON merge_condition
   { WHEN MATCHED [ AND matched_condition ] THEN matched_action |
    WHEN NOT MATCHED [ AND not_matched_condition ] THEN not_matched_action |
    WHEN NOT MATCHED [ AND not_matched_by_source_condition ] THEN not_matched_by_source_action } 
    ...
-- Parameter Explanation
matched_action ::=
    UPDATE SET <column_name> = <expr> [ , <column_name2> = <expr2> ... ] 
    | DELETE 
not_matched_action ::=
    INSERT [ ( <column_name> [ , ... ] ) ] VALUES ( <expr> [ , ... ] )

Parameter Description

• target_table: Specifies the target table, aliases can be used.
• source_table: Specifies the table or subquery to be compared with the target table, aliases can be used.
• merge_condition: Defines how rows from the source table match with rows from the target table, returns a boolean expression.
• WHEN MATCHED [ AND matched_condition ]: Executes the WHEN MATCHED clause when a source row matches a target table row based on merge_condition and optional matched_condition.
  • If no WHEN MATCHED condition evaluates to true for a pair of source and target rows that match the merge_condition, the target row remains unchanged.
  • If there are multiple WHEN MATCHED clauses, they are evaluated in the order specified. Each WHEN MATCHED clause must have a matched_condition.
• WHEN NOT MATCHED [ AND not_matched_condition ]: Executes the WHEN NOT MATCHED clause when a source row does not match any row in the target table based on merge_condition and optional not_matched_condition.
  • If there are multiple WHEN NOT MATCHED clauses, they are evaluated in the order specified.
• matched_action:
  • DELETE: Deletes the matching target table row.
  • UPDATE: Updates the matching target table row. Use UPDATE SET column1 = source.column1 [, column2 = source.column2 ...].
• not_matched_action:
  • INSERT: Inserts into the target table using the corresponding columns from the source dataset, supports specifying fields for insertion. For unspecified target columns, NULL is inserted.

Notes

When executing the MERGE INTO statement, the system will report an error if it may produce uncertain results. The reasons for this situation include:

1. If the ON clause causes more than one row from the source table to match a row in the target table, the SQL standard requires an error to be raised.
2. If the ON clause causes more than one row from the source table to match a row in the target table, and there are still multiple records after filtering with the AND case_predicate condition.
3. When both WHEN MATCHED and WHEN NOT MATCHED statements are present, the first statement in the MERGE INTO statement must be WHEN MATCHED, otherwise a syntax error will occur. The following is an incorrect example:

    --Incorrect usage
    MERGE INTO target USING source
      ON target.key = source.key
      WHEN NOT MATCHED THEN INSERT (col1, col2) VALUES (source.col1, source.col2)
      WHEN MATCHED THEN UPDATE SET target.col1 = source.col1, target.col2 = source.col2;
    --Correct usage
    MERGE INTO target USING source
      ON target.key = source.key
      WHEN MATCHED THEN UPDATE SET target.col1 = source.col1, target.col2 = source.col2
      WHEN NOT MATCHED THEN INSERT (col1, col2) VALUES (source.col1, source.col2);
    ```
The situation of deterministic results:

1. If the `ON` clause matches 1 row in the source table with a row in the target table.
2. If the `ON` clause matches more than 1 row in the source table with a row in the target table, and only one record remains after filtering with the `AND case_predicate` condition.

## Usage Example
```SQL
-- When the match condition is met, delete data from the target table
MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED THEN DELETE

-- Use column1 from the source table to update column1 in the target table
MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED AND target.updated_at < source.updated_at THEN UPDATE SET target.col1 = source.col1

-- If the key matches in both the source and target tables and there is a deletion mark in the target table, delete the data. If the key matches but there is no deletion mark, update the target table
MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED AND target.marked_for_deletion THEN DELETE
  WHEN MATCHED THEN UPDATE SET target.updated_at = source.updated_at, target.value = source.value

-- Insert new data, if the key already exists, update the values in the target table
MERGE INTO target USING source
  ON target.key = source.key
  WHEN MATCHED THEN UPDATE SET target.col1 = source.col1, target.col2 = source.col2
  WHEN NOT MATCHED THEN INSERT (col1, col2) VALUES (source.col1, source.col2)