<!--
doc/src/sgml/ref/merge.sgml
PostgreSQL documentation
-->

<refentry id="sql-merge">
 <indexterm zone="sql-merge">
  <primary>MERGE</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>MERGE</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>MERGE</refname>
  <refpurpose>conditionally insert, update, or delete rows of a table</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
[ WITH <replaceable class="parameter">with_query</replaceable> [, ...] ]
MERGE INTO [ ONLY ] <replaceable class="parameter">target_table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ]
USING <replaceable class="parameter">data_source</replaceable> ON <replaceable class="parameter">join_condition</replaceable>
<replaceable class="parameter">when_clause</replaceable> [...]
[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ]
            { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ]

<phrase>where <replaceable class="parameter">data_source</replaceable> is:</phrase>

{ [ ONLY ] <replaceable class="parameter">source_table_name</replaceable> [ * ] | ( <replaceable class="parameter">source_query</replaceable> ) } [ [ AS ] <replaceable class="parameter">source_alias</replaceable> ]

<phrase>and <replaceable class="parameter">when_clause</replaceable> is:</phrase>

{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
  WHEN NOT MATCHED BY SOURCE [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> | DO NOTHING } |
  WHEN NOT MATCHED [ BY TARGET ] [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } }

<phrase>and <replaceable class="parameter">merge_insert</replaceable> is:</phrase>

INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES }

<phrase>and <replaceable class="parameter">merge_update</replaceable> is:</phrase>

UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } |
             ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) |
             ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> )
           } [, ...]

<phrase>and <replaceable class="parameter">merge_delete</replaceable> is:</phrase>

DELETE
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>MERGE</command> performs actions that modify rows in the
   target table identified as <replaceable class="parameter">target_table_name</replaceable>,
   using the <replaceable class="parameter">data_source</replaceable>.
   <command>MERGE</command> provides a single <acronym>SQL</acronym>
   statement that can conditionally <command>INSERT</command>,
   <command>UPDATE</command> or <command>DELETE</command> rows, a task
   that would otherwise require multiple procedural language statements.
  </para>

  <para>
   First, the <command>MERGE</command> command performs a join
   from <replaceable class="parameter">data_source</replaceable> to
   the target table
   producing zero or more candidate change rows.  For each candidate change
   row,