MERGE

Name

MERGE -- update, insert or delete rows of a table based upon source data

Synopsis

MERGE INTO table [ [ AS ] alias ]
USING source-query
ON join_condition
[when_clause [...]]

where when_clause is

{ WHEN MATCHED [ AND condition ] THEN { merge_update | DELETE }
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }

where merge_update is

UPDATE SET { column = { expression | DEFAULT } |
           ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_insert is

INSERT [( column [, ...] )] { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

Description

MERGE performs at most one action on rows from the target table, driven by the rows from the source query. This provides a way to specify a single SQL statement that can conditionally UPDATE or INSERT rows, a task that would otherwise require multiple procedural language statements.

First, the MERGE command performs a left outer join from source query to target table, producing zero or more merged rows. For each merged row, WHEN clauses are evaluated in the specified order until one of them is activated. The corresponding action is then applied and processing continues for the next row.

MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same names, though the syntax is slightly different.

If no WHEN clause activates then an implicit action of INSERT DEFAULT VALUES is performed for that row. If that implicit action is not desirable an explicit action of DO NOTHING may be specified instead.

MERGE will only affect rows only in the specified table.

There is no RETURNING clause with MERGE.

There is no MERGE privilege. You must have the UPDATE privilege on the table if you specify an update action, the INSERT privilege if you specify an insert action and/or the DELETE privilege if you wish to delete. You will also require the SELECT privilege to any table whose values are read in the expressions or condition.

Parameters

table

The name (optionally schema-qualified) of the table to merge into.

alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given MERGE foo AS f, the remainder of the MERGE statement must refer to this table as f not foo.

source-query

A query (SELECT statement or VALUES statement) that supplies the rows to be merged into the target table. Refer to the SELECT statement or VALUES statement for a description of the syntax.

join_condition

join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in the join are considered to match. You should ensure that the join produces at most one output row for each row to be modified. An attempt to modify any row of the target table more than once will result in an error. This behaviour requires the user to take greater care in using MERGE, though is required explicitly by the SQL Standard.

condition

An expression that returns a value of type boolean. If this expression returns true then the WHEN clause will be activated and the corresponding action will occur for that row.

merge_update

The specification of an UPDATE action. Do not include the table name, as you would normally do with an UPDATE command. For example, UPDATE tab SET col = 1 is invalid. Also, do not include a WHERE clause, since only the current can be updated. For example, UPDATE SET col = 1 WHERE key = 57 is invalid.

merge_insert

The specification of an INSERT action. Do not include the table name, as you would normally do with an INSERT command. For example, INSERT INTO tab VALUES (1, 50) is invalid.

column

The name of a column in table. The column name can be qualified with a subfield name or array subscript, if needed. Do not include the table's name in the specification of a target column — for example, UPDATE SET tab.col = 1 is invalid.

expression

An expression to assign to the column. The expression can use the old values of this and other columns in the table.

DEFAULT

Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).

Outputs

On successful completion, a MERGE command returns a command tag of the form

MERGE total-count

The total-count is the number of rows changed (either updated, inserted or deleted). If total-count is 0, no rows were changed (this is not considered an error).

The number of rows updated, inserted or deleted is not available as part of the command tag. An optional NOTIFY message can be generated to present this information, if desired.

NOTIFY:  34 rows processed: 11 updated, 5 deleted, 15 inserted, 3 default inserts, 0 no action

Notes

What essentially happens is that the target table is left outer-joined to the tables mentioned in the source-query, and each output row of the join may then activate at most one when-clause. The row will be matched only once per statement, so the status of MATCHED or NOT MATCHED cannot change once testing of WHEN clauses has begun. MERGE will not invoke Rules.

The following steps take place during the execution of MERGE.

  1. Perform any BEFORE STATEMENT triggers for actions specified, whether or not they actually occur.

  2. Perform left outer join from source to target table. Then for each row:

    1. Evaluate whether each row is MATCHED or NOT MATCHED.

    2. Test each WHEN condition in the order specified until one activates. Identify the action and its event type.

    3. Perform any BEFORE ROW triggers that fire for the action's event type.

    4. Apply the action specified.

    5. Perform any AFTER ROW triggers that fire for the action's event type.

  3. Perform any AFTER STATEMENT triggers for actions specified, whether or not they actually occur.

In summary, statement triggers for an event type (say, INSERT) will be fired whenever we specify an action of that kind. Row-level triggers will fire only for event type activated. So a MERGE might fire statement triggers for both UPDATE and INSERT, even though only UPDATE row triggers were fired.

Examples

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item.

MERGE INTO wines w
USING (VALUES('Chateau Lafite 2003', '24')) v
ON v.column1 = w.winename
WHEN NOT MATCHED 
  INSERT VALUES(v.column1, v.column2)
WHEN MATCHED
  UPDATE SET stock = stock + v.column2;

Perform maintenance on CustomerAccounts based upon new Transactions. The following statement will fail if any accounts have had more than one transaction

MERGE CustomerAccount CA

USING (SELECT CustomerId, TransactionValue, 
       FROM Transactions
       WHERE TransactionId > 35345678) AS T

ON T.CustomerId = CA.CustomerId

WHEN MATCHED 
  UPDATE SET Balance = Balance - TransactionValue

WHEN NOT MATCHED
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue)
;

so the right way to do this is to pre-aggregate the data

MERGE CustomerAccount CA

USING (SELECT CustomerId, Sum(TransactionValue) As TransactionSum
       FROM Transactions
       WHERE TransactionId > 35345678
       GROUP BY CustomerId) AS T

ON T.CustomerId = CA.CustomerId

WHEN MATCHED 
  UPDATE SET Balance = Balance - TransactionSum

WHEN NOT MATCHED
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionSum)
;

Compatibility

This command conforms to the SQL standard, except that the DELETE and DO NOTHING actions are PostgreSQL extensions.

According to the standard, the column-list syntax for an UPDATE action should allow a list of columns to be assigned from a single row-valued expression. This is not currently implemented — the source must be a list of independent expressions.