<para>

<command>MERGE</command> performs actions that modify rows in the

using the <replaceable class="parameter">data_source</replaceable>.

<command>MERGE</command> provides a single <acronym>SQL</acronym>

statement that can conditionally <command>INSERT</command>,

<para>

First, the <command>MERGE</command> command performs a join

from <replaceable class="parameter">data_source</replaceable> to

producing zero or more candidate change rows. For each candidate change

row, the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal>

is set just once, after which <literal>WHEN</literal> clauses are evaluated

<command>DELETE</command> commands of the same names. The syntax of

those commands is different, notably that there is no <literal>WHERE</literal>

clause and no table name is specified. All actions refer to the

though modifications to other tables may be made using triggers.

</para>

There is no separate <literal>MERGE</literal> privilege.

If you specify an update action, you must have the

<literal>UPDATE</literal> privilege on the column(s)

that are referred to in the <literal>SET</literal> clause.

If you specify an insert action, you must have the <literal>INSERT</literal>

If you specify a delete action, you must have the <literal>DELETE</literal>

Privileges are tested once at statement start and are checked

whether or not particular <literal>WHEN</literal> clauses are executed.

</para>

<para>

<command>MERGE</command> is not supported if the

materialized view, foreign table, or if it has any

rules defined on it.

</para>

<para>

A query (<command>SELECT</command> statement or <command>VALUES</command>

statement) that supplies the rows to be merged into the

Refer to the <xref linkend="sql-select"/>

statement or <xref linkend="sql-values"/>

statement for a description of the syntax.

<type>boolean</type> (similar to a <literal>WHERE</literal>

clause) that specifies which rows in the

<replaceable class="parameter">data_source</replaceable>

</para>

<warning>

<para>

that attempt to match <replaceable class="parameter">data_source</replaceable>

rows should appear in <replaceable class="parameter">join_condition</replaceable>.

<replaceable class="parameter">join_condition</replaceable> subexpressions that

columns can affect which action is taken, often in surprising ways.

</para>

</warning>

<para>

If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal>

and the candidate change row matches a row in the

the <literal>WHEN</literal> clause is executed if the

<replaceable class="parameter">condition</replaceable> is

absent or it evaluates to <literal>true</literal>.

Conversely, if the <literal>WHEN</literal> clause specifies

<literal>WHEN NOT MATCHED</literal>

and the candidate change row does not match a row in the

the <literal>WHEN</literal> clause is executed if the

<replaceable class="parameter">condition</replaceable> is

absent or it evaluates to <literal>true</literal>.

or null if there is none.

</para>

<para>

is a partitioned table, each row is routed to the appropriate partition

and inserted into it.

is a partition, an error will occur if any input row violates the

partition constraint.

</para>

<listitem>

<para>

The specification of an <literal>UPDATE</literal> action that updates

Column names may not be specified more than once.

</para>

<para>

<listitem>

<para>

Specifies a <literal>DELETE</literal> action that deletes the current row

Do not include the table name or any other clauses, as you would normally

do with a <xref linkend="sql-delete"/> command.

</para>

<term><replaceable class="parameter">column_name</replaceable></term>

<listitem>

<para>

can be qualified with a subfield name or array subscript, if

needed. (Inserting into only some fields of a composite

column leaves the other fields null.)