The MERGE statement to update a table

The MERGE statement allows you to apply a Boolean condition to the result of an outer join of a source table and a target table. If the MERGE statement includes the Update clause, rows that satisfy the join condition that you specify after the ON keyword are used in UPDATE operations on the target. The SET clause of the MERGE statement supports the same syntax as the SET clause of the UPDATE statement, and specifies which columns of the target table to update.

MERGE INTO t_target AS t USING t_source AS s ON t.col_a = s.col_a
   WHEN MATCHED THEN UPDATE 
      SET t.col_b = t.col_b + s.col_b ;

In the preceding example, the name of the target table is t_target and the name of the source table is t_source. For rows of the join result where col_a has the same value in both the source and the target tables, the MERGE statement updates the t_target table by adding the value of column col_b in the source table to the current value of the col_b column in the t_target table.

An UPDATE operation of the MERGE statement does not modify the source table, and cannot update any row in the target table more than once.

A single MERGE statement can combine both UPDATE and INSERT operations, or can combine both DELETE and INSERT operations but the delete clause is not required. For a different example of MERGE that includes no Update clause, see the topic The Delete clause of MERGE