Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/ref/merge.sgml`
d7b76e0a66373715341742bc667ec0f630c67059b9fbe1cd0000000100000fa1
 which action is taken, often in surprising ways.
      </para>
      <para>
       If both <literal>WHEN NOT MATCHED BY SOURCE</literal> and
       <literal>WHEN NOT MATCHED [BY TARGET]</literal> clauses are specified,
       the <command>MERGE</command> command will perform a <literal>FULL</literal>
       join between <replaceable class="parameter">data_source</replaceable>
       and the target table.  For this to work, at least one
       <replaceable class="parameter">join_condition</replaceable> subexpression
       must use an operator that can support a hash join, or all of the
       subexpressions must use operators that can support a merge join.
      </para>
     </warning>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">when_clause</replaceable></term>
    <listitem>
     <para>
      At least one <literal>WHEN</literal> clause is required.
     </para>
     <para>
      The <literal>WHEN</literal> clause may specify <literal>WHEN MATCHED</literal>,
      <literal>WHEN NOT MATCHED BY SOURCE</literal>, or
      <literal>WHEN NOT MATCHED [BY TARGET]</literal>.
      Note that the <acronym>SQL</acronym> standard only defines
      <literal>WHEN MATCHED</literal> and <literal>WHEN NOT MATCHED</literal>
      (which is defined to mean no matching target row).
      <literal>WHEN NOT MATCHED BY SOURCE</literal> is an extension to the
      <acronym>SQL</acronym> standard, as is the option to append
      <literal>BY TARGET</literal> to <literal>WHEN NOT MATCHED</literal>, to
      make its meaning more explicit.
     </para>
     <para>
      If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal>
      and the candidate change row matches a row in the
      <replaceable class="parameter">data_source</replaceable> to a row in the
      target table, the <literal>WHEN</literal> clause is executed if the
      <replaceable class="parameter">condition</replaceable> is
      absent or it evaluates to <literal>true</literal>.
     </para>
     <para>
      If the <literal>WHEN</literal> clause specifies
      <literal>WHEN NOT MATCHED BY SOURCE</literal> and the candidate change
      row represents a row in the target table that does not match a row in the
      <replaceable class="parameter">data_source</replaceable>, the
      <literal>WHEN</literal> clause is executed if the
      <replaceable class="parameter">condition</replaceable> is
      absent or it evaluates to <literal>true</literal>.
     </para>
     <para>
      If the <literal>WHEN</literal> clause specifies
      <literal>WHEN NOT MATCHED [BY TARGET]</literal> and the candidate change
      row represents a row in the
      <replaceable class="parameter">data_source</replaceable> that does not
      match a row in the target table,
      the <literal>WHEN</literal> clause is executed if the
      <replaceable class="parameter">condition</replaceable> is
      absent or it evaluates to <literal>true</literal>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">condition</replaceable></term>
    <listitem>
     <para>
      An expression that returns a value of type <type>boolean</type>.
      If this expression for a <literal>WHEN</literal> clause
      returns <literal>true</literal>, then the action for that clause
      is executed for that row.
     </para>
     <para>
      A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns
      in both the source and the target relations. A condition on a
      <literal>WHEN NOT MATCHED BY SOURCE</literal> clause can only refer to
      columns from the target relation, since by definition there is no matching
      source row. A condition on a <literal>WHEN NOT MATCHED [BY TARGET]</literal>
      clause can only refer to columns from
      the source relation, since by definition there is no matching target row.
      Only the system attributes from the target table
Title: Understanding WHEN Clauses and Conditions in MERGE Statements
Summary
This section explains the usage of 'WHEN' clauses in MERGE statements, highlighting 'WHEN MATCHED', 'WHEN NOT MATCHED BY SOURCE', and 'WHEN NOT MATCHED [BY TARGET]'. It clarifies when each clause is executed based on matching rows between the target and data source. Additionally, it explains the 'condition' parameter, a boolean expression that determines if the action for a 'WHEN' clause is executed, specifying which relations' columns can be referenced in each type of 'WHEN' clause.