MERGE Specification

[Simon Riggs <simon(at)2ndquadrant(dot)com>]

The following two files specify the behaviour of the MERGE statement and
how it will work in the world of PostgreSQL. In places, this supercedes
my recent commentary on MERGE, particularly with regard to triggers.

Neither of these files is intended for CVS. 

The HTML file was generated from SGML source, though the latter is not
included here for clarity.

The test file shows how I expect a successful test run to look when a
regression test is executed with a working version of final MERGE patch
applied. It has behavioural comments in it also, to make it slightly
more readable.

If anybody has any questions, ask them now please, before I begin
detailed implementation.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com

--
-- MERGE
--
CREATE TABLE target (id integer, balance integer);
CREATE TABLE source (id integer, balance integer);
INSERT INTO target VALUES (1, 10);
INSERT INTO target VALUES (2, 20);
INSERT INTO target VALUES (3, 30);
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
  2 |      20
  3 |      30
(3 rows)

--
-- initial tests
--
-- empty source means 0 rows touched
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED THEN
	UPDATE SET balance = t.balance + s.balance
;
-- insert some source rows to work from
INSERT INTO source VALUES (2, 5);
INSERT INTO source VALUES (3, 20);
INSERT INTO source VALUES (4, 40);
SELECT * FROM source;
 id | balance 
----+---------
  2 |       5
  3 |      20
  4 |      40
(3 rows)

-- do a simple equivalent of an UPDATE join
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED THEN
	UPDATE SET balance = t.balance + s.balance
;
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
  2 |      25
  3 |      50
(3 rows)

ROLLBACK;
-- do a simple equivalent of an INSERT SELECT
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
  2 |      20
  3 |      30
  4 |      40
(4 rows)

ROLLBACK;
-- now the classic UPSERT
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED THEN
	UPDATE SET balance = t.balance + s.balance
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
  2 |      25
  3 |      50
  4 |      40
(4 rows)

ROLLBACK;
--
-- Non-standard functionality
-- 
-- do a simple equivalent of a DELETE join
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED THEN
	DELETE
;
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
(1 row)

ROLLBACK;
-- now the classic UPSERT, with a DELETE
-- the Standard doesn't allow the DELETE clause for some reason,
-- though other implementations do
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED AND s.balance > 10 THEN
	UPDATE SET balance = t.balance + s.balance
WHEN MATCHED THEN
	DELETE
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
  3 |      50
  4 |      40
(3 rows)

ROLLBACK;
-- Prepare the test data to generate multiple matching rows for a single target
INSERT INTO source VALUES (3, 5);
SELECT * FROM source ORDER BY id, balance;
 id | balance 
----+---------
  2 |       5
  3 |       5
  3 |      20
  4 |      40
(4 rows)

-- we now have a duplicate key in source, so when we join to
-- target we will generate 2 matching rows, not one
-- In the following statement row id=3 will be both updated
-- and deleted by this statement and so will cause a run-time error
-- when the second change to that row is detected
-- This next SQL statement
--  fails according to standard
--  fails in PostgreSQL implementation
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED AND s.balance > 10 THEN
	UPDATE SET balance = t.balance + s.balance
WHEN MATCHED THEN
	DELETE
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
;
ERROR:  multiple actions on single target row
 
ROLLBACK;

-- This next SQL statement
--  fails according to standard
--  suceeds in PostgreSQL implementation by simply ignoring the second
--  matching row since it activates no WHEN clause
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED AND s.balance > 10 THEN
	UPDATE SET balance = t.balance + s.balance
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
;
ROLLBACK;
-- Now lets prepare the test data to generate 2 non-matching rows
DELETE FROM source WHERE id = 3 AND balance = 5;
INSERT INTO source VALUES (4, 5);
SELECT * FROM source;
 id | balance 
----+---------
  2 |       5
  3 |      20
  4 |       5
  4 |      40
(4 rows)

-- This next SQL statement
--  suceeds according to standard (yes, it is inconsistent)
--  suceeds in PostgreSQL implementation, though could easily fail if
--  there was an appropriate unique constraint
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
  2 |      20
  3 |      30
  4 |       5
  4 |      40
(5 rows)

ROLLBACK;
-- This next SQL statement works, but since there is no WHEN clause that
-- applies to non-matching rows, SQL standard requires us to generate
-- rows with DEFAULT VALUES for all columns, which is why we support the
-- syntax DO NOTHING (similar to the way Rules work) in addition
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN NOT MATCHED AND s.balance > 100 THEN
	INSERT VALUES (s.id, s.balance)
;
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
  2 |      20
  3 |      30
    |
    |
(5 rows)

ROLLBACK;
-- This next SQL statement suceeds, but does nothing since there are
-- only non-matching rows that do not activate a WHEN clause, so we
-- provide syntax to just ignore them, rather than allowing data quality
-- problems
BEGIN;
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN NOT MATCHED AND s.balance > 100 THEN
	INSERT VALUES (s.id, s.balance)
WHEN NOT MATCHED
	DO NOTHING
;
SELECT * FROM target;
 id | balance 
----+---------
  1 |      10
  2 |      20
  3 |      30
(3 rows)

ROLLBACK;
--
-- Weirdness
--
-- MERGE statement containing WHEN clauses that are never executable
-- NOT an error under the standard
MERGE into target t
USING (select * from source) AS s
ON t.id = s.id
WHEN MATCHED AND s.balance > 0 THEN
	UPDATE SET balance = t.balance + s.balance
WHEN MATCHED THEN
	DELETE
WHEN NOT MATCHED THEN
	INSERT VALUES (s.id, s.balance)
WHEN NOT MATCHED THEN /* never executed because of order of WHEN clauses */
	INSERT VALUES (s.id, s.balance + 10)
WHEN MATCHED THEN /* never executed because of order of WHEN clauses */
	UPDATE SET balance = t.balance + s.balance
;

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd";>
<HTML
><HEAD
><TITLE
>MERGE</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs(at)postgresql(dot)org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.4devel Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="LOCK"
HREF="sql-lock.html"><LINK
REL="NEXT"
TITLE="MOVE"
HREF="sql-move.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2008-04-21T20:00:13"></HEAD
><BODY
CLASS="REFENTRY"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.4devel Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-lock.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-lock.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-move.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-move.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="SQL-MERGE"
></A
>MERGE</H1
><DIV
CLASS="REFNAMEDIV"
><A
NAME="AEN59006"
></A
><H2
>Name</H2
>MERGE&nbsp;--&nbsp;update, insert or delete rows of a table based upon source data</DIV
><A
NAME="AEN59009"
></A
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN59011"
></A
><H2
>Synopsis</H2
><PRE
CLASS="SYNOPSIS"
>MERGE INTO <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
> [ [ AS ] <TT
CLASS="REPLACEABLE"
><I
>alias</I
></TT
> ]
USING <TT
CLASS="REPLACEABLE"
><I
>source-query</I
></TT
>
ON <TT
CLASS="REPLACEABLE"
><I
>join_condition</I
></TT
>
[<TT
CLASS="REPLACEABLE"
><I
>when_clause</I
></TT
> [...]]

where <TT
CLASS="REPLACEABLE"
><I
>when_clause</I
></TT
> is

{ WHEN MATCHED [ AND <TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
> ] THEN { <TT
CLASS="REPLACEABLE"
><I
>merge_update</I
></TT
> | DELETE }
  WHEN NOT MATCHED [ AND <TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
> ] THEN { <TT
CLASS="REPLACEABLE"
><I
>merge_insert</I
></TT
> | DO NOTHING } }

where <TT
CLASS="REPLACEABLE"
><I
>merge_update</I
></TT
> is

UPDATE SET { <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> = { <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> | DEFAULT } |
           ( <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [, ...] ) = ( { <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> | DEFAULT } [, ...] ) } [, ...]

and <TT
CLASS="REPLACEABLE"
><I
>merge_insert</I
></TT
> is

INSERT [( <TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
> [, ...] )] { VALUES ( { <TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
> | DEFAULT } [, ...] ) | DEFAULT VALUES }</PRE
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN59031"
></A
><H2
>Description</H2
><P
>   <TT
CLASS="COMMAND"
>MERGE</TT
> 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
   <TT
CLASS="COMMAND"
>UPDATE</TT
> or <TT
CLASS="COMMAND"
>INSERT</TT
> rows, a task
   that would otherwise require multiple procedural language statements.
  </P
><P
>   First, the <TT
CLASS="COMMAND"
>MERGE</TT
> command performs a left outer join
   from source query to target table, producing zero or more merged rows. For 
   each merged row, <TT
CLASS="LITERAL"
>WHEN</TT
> 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.
  </P
><P
>   <TT
CLASS="COMMAND"
>MERGE</TT
> actions have the same effect as 
   regular <TT
CLASS="COMMAND"
>UPDATE</TT
>, <TT
CLASS="COMMAND"
>INSERT</TT
>, or
   <TT
CLASS="COMMAND"
>DELETE</TT
> commands of the same names, though the syntax
   is slightly different.
  </P
><P
>   If no <TT
CLASS="LITERAL"
>WHEN</TT
> clause activates then an implicit action of 
   <TT
CLASS="LITERAL"
>INSERT DEFAULT VALUES</TT
> is performed for that row. If that
   implicit action is not desirable an explicit action of 
   <TT
CLASS="LITERAL"
>DO NOTHING</TT
> may be specified instead.
  </P
><P
>   <TT
CLASS="COMMAND"
>MERGE</TT
> will only affect rows only in the specified table.
  </P
><P
>   There is no <TT
CLASS="LITERAL"
>RETURNING</TT
> clause with <TT
CLASS="COMMAND"
>MERGE</TT
>.
  </P
><P
>   There is no MERGE privilege.
   You must have the <TT
CLASS="LITERAL"
>UPDATE</TT
> privilege on the table
   if you specify an update action, the <TT
CLASS="LITERAL"
>INSERT</TT
> privilege if
   you specify an insert action and/or the <TT
CLASS="LITERAL"
>DELETE</TT
> privilege
   if you wish to delete. You will also require the 
   <TT
CLASS="LITERAL"
>SELECT</TT
> privilege to any table whose values are read 
   in the <TT
CLASS="REPLACEABLE"
><I
>expressions</I
></TT
> or
   <TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
>.
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN59061"
></A
><H2
>Parameters</H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
></DT
><DD
><P
>      The name (optionally schema-qualified) of the table to merge into.
     </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>alias</I
></TT
></DT
><DD
><P
>      A substitute name for the target table. When an alias is
      provided, it completely hides the actual name of the table.  For
      example, given <TT
CLASS="LITERAL"
>MERGE foo AS f</TT
>, the remainder of the
      <TT
CLASS="COMMAND"
>MERGE</TT
> statement must refer to this table as
      <TT
CLASS="LITERAL"
>f</TT
> not <TT
CLASS="LITERAL"
>foo</TT
>.
     </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>source-query</I
></TT
></DT
><DD
><P
>      A query (<TT
CLASS="COMMAND"
>SELECT</TT
> statement or <TT
CLASS="COMMAND"
>VALUES</TT
>
      statement) that supplies the rows to be merged into the target table.
      Refer to the <A
HREF="sql-select.html"
><I
>SELECT</I
></A
>
      statement or <A
HREF="sql-values.html"
><I
>VALUES</I
></A
> 
      statement for a description of the syntax.
     </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>join_condition</I
></TT
></DT
><DD
><P
>      <TT
CLASS="REPLACEABLE"
><I
>join_condition</I
></TT
> is
      an expression resulting in a value of type
      <TT
CLASS="TYPE"
>boolean</TT
> (similar to a <TT
CLASS="LITERAL"
>WHERE</TT
>
      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 <TT
CLASS="COMMAND"
>MERGE</TT
>,
      though is required explicitly by the SQL Standard.
     </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
></DT
><DD
><P
>      An expression that returns a value of type <TT
CLASS="TYPE"
>boolean</TT
>.
      If this expression returns <TT
CLASS="LITERAL"
>true</TT
> then the <TT
CLASS="LITERAL"
>WHEN</TT
>
	  clause will be activated and the corresponding action will occur for
      that row.
     </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>merge_update</I
></TT
></DT
><DD
><P
>      The specification of an <TT
CLASS="LITERAL"
>UPDATE</TT
> action.  Do not include
      the table name, as you would normally do with an 
      <A
HREF="sql-update.html"
><I
>UPDATE</I
></A
> command.
      For example, <TT
CLASS="LITERAL"
>UPDATE tab SET col = 1</TT
> is invalid. Also,
      do not include a <TT
CLASS="LITERAL"
>WHERE</TT
> clause, since only the current
      can be updated. For example, 
      <TT
CLASS="LITERAL"
>UPDATE SET col = 1 WHERE key = 57</TT
> is invalid.
     </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>merge_insert</I
></TT
></DT
><DD
><P
>      The specification of an <TT
CLASS="LITERAL"
>INSERT</TT
> action.  Do not include
      the table name, as you would normally do with an 
      <A
HREF="sql-insert.html"
><I
>INSERT</I
></A
> command.
      For example, <TT
CLASS="LITERAL"
>INSERT INTO tab VALUES (1, 50)</TT
> is invalid.
     </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>column</I
></TT
></DT
><DD
><P
>      The name of a column in <TT
CLASS="REPLACEABLE"
><I
>table</I
></TT
>.
      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 &mdash; for example,
      <TT
CLASS="LITERAL"
>UPDATE SET tab.col = 1</TT
> is invalid.
     </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>expression</I
></TT
></DT
><DD
><P
>      An expression to assign to the column.  The expression can use the
      old values of this and other columns in the table.
     </P
></DD
><DT
><TT
CLASS="LITERAL"
>DEFAULT</TT
></DT
><DD
><P
>      Set the column to its default value (which will be NULL if no
      specific default expression has been assigned to it).
     </P
></DD
></DL
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN59139"
></A
><H2
>Outputs</H2
><P
>   On successful completion, a <TT
CLASS="COMMAND"
>MERGE</TT
> command returns a command
   tag of the form
</P><PRE
CLASS="SCREEN"
>MERGE <TT
CLASS="REPLACEABLE"
><I
>total-count</I
></TT
></PRE
><P>
   The <TT
CLASS="REPLACEABLE"
><I
>total-count</I
></TT
> is the number
   of rows changed (either updated, inserted or deleted).  
   If <TT
CLASS="REPLACEABLE"
><I
>total-count</I
></TT
> is 0, no rows
   were changed (this is not considered an error).
  </P
><P
>   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.
</P><PRE
CLASS="SCREEN"
>NOTIFY:  34 rows processed: 11 updated, 5 deleted, 15 inserted, 3 default inserts, 0 no action</PRE
><P>
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN59149"
></A
><H2
>Notes</H2
><P
>   What essentially happens is that the target table is left outer-joined to 
   the tables mentioned in the <TT
CLASS="REPLACEABLE"
><I
>source-query</I
></TT
>, 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
   <TT
CLASS="LITERAL"
>MATCHED</TT
> or <TT
CLASS="LITERAL"
>NOT MATCHED</TT
> cannot change once testing
   of <TT
CLASS="LITERAL"
>WHEN</TT
> clauses has begun. <TT
CLASS="COMMAND"
>MERGE</TT
> will not
   invoke Rules.
  </P
><P
>   The following steps take place during the execution of 
   <TT
CLASS="COMMAND"
>MERGE</TT
>. 
    <P
></P
></P><OL
TYPE="1"
><LI
><P
>       Perform any BEFORE STATEMENT triggers for actions specified, whether or
       not they actually occur.
      </P
></LI
><LI
><P
>       Perform left outer join from source to target table. Then for each row:
       <P
></P
></P><OL
TYPE="a"
><LI
><P
>          Evaluate whether each row is MATCHED or NOT MATCHED.
         </P
></LI
><LI
><P
>          Test each WHEN condition in the order specified until one activates.
          Identify the action and its event type.
         </P
></LI
><LI
><P
>          Perform any BEFORE ROW triggers that fire for the action's event type.
         </P
></LI
><LI
><P
>          Apply the action specified.
         </P
></LI
><LI
><P
>          Perform any AFTER ROW triggers that fire for the action's event type.
         </P
></LI
></OL
><P>
      </P
></LI
><LI
><P
>       Perform any AFTER STATEMENT triggers for actions specified, whether or 
       not they actually occur. 
      </P
></LI
></OL
><P>
   In summary, statement triggers for an event type (say, INSERT) will
   be fired whenever we <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>specify</I
></SPAN
> an action of that kind. Row-level
   triggers will fire only for event type <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>activated</I
></SPAN
>.
   So a <TT
CLASS="COMMAND"
>MERGE</TT
> might fire statement triggers for both
   <TT
CLASS="LITERAL"
>UPDATE</TT
> and <TT
CLASS="LITERAL"
>INSERT</TT
>, even though only 
   <TT
CLASS="LITERAL"
>UPDATE</TT
> row triggers were fired.
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN59183"
></A
><H2
>Examples</H2
><P
>   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. 
</P><PRE
CLASS="PROGRAMLISTING"
>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;</PRE
><P>
  </P
><P
>   Perform maintenance on CustomerAccounts based upon new Transactions.
   The following statement will fail if any accounts have had more than
   one transaction

</P><PRE
CLASS="PROGRAMLISTING"
>MERGE CustomerAccount CA

USING (SELECT CustomerId, TransactionValue, 
       FROM Transactions
       WHERE TransactionId &#62; 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)
;</PRE
><P>

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

</P><PRE
CLASS="PROGRAMLISTING"
>MERGE CustomerAccount CA

USING (SELECT CustomerId, Sum(TransactionValue) As TransactionSum
       FROM Transactions
       WHERE TransactionId &#62; 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)
;</PRE
><P>
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN59190"
></A
><H2
>Compatibility</H2
><P
>   This command conforms to the <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> standard, except
   that the <TT
CLASS="LITERAL"
>DELETE</TT
> and <TT
CLASS="LITERAL"
>DO NOTHING</TT
> actions
   are <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> extensions.
  </P
><P
>   According to the standard, the column-list syntax for an <TT
CLASS="LITERAL"
>UPDATE</TT
>
   action should allow a list of columns to be assigned from a single
   row-valued expression. 
   This is not currently implemented &mdash; the source must be a list
   of independent expressions.
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-lock.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-move.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>LOCK</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>MOVE</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>