MERGE SQL - Language Statements MERGE update, insert or delete rows of a table based upon source data MERGE 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 } MERGE performs at most one action on each row 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. 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 statement or 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 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 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). 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 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. Perform any BEFORE STATEMENT triggers for actions specified, whether or not they actually occur. Perform left outer join from source to target table. Then for each row: Evaluate whether each row is MATCHED or NOT MATCHED. Test each WHEN condition in the order specified until one activates. Identify the action and its event type. Perform any BEFORE ROW triggers that fire for the action's event type. Apply the action specified. Perform any AFTER ROW triggers that fire for the action's event type. 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. 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) ; 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.