<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="insert-on-duplicate">
      </a>
      15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement
     </h4>
    </div>
   </div>
  </div>
  <p>
   If you specify an
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   clause and a row to be inserted would cause a duplicate value in
        a
   <code class="literal">
    UNIQUE
   </code>
   index or
   <code class="literal">
    PRIMARY
        KEY
   </code>
   , an
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   of the
        old row occurs. For example, if column
   <code class="literal">
    a
   </code>
   is
        declared as
   <code class="literal">
    UNIQUE
   </code>
   and contains the value
   <code class="literal">
    1
   </code>
   , the following two statements have similar
        effect:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59006052"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span>
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> c<span class="token operator">=</span>c<span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">;</span>

<span class="token keyword">UPDATE</span> t1 <span class="token keyword">SET</span> c<span class="token operator">=</span>c<span class="token operator">+</span><span class="token number">1</span> <span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The effects are not quite identical: For an
   <code class="literal">
    InnoDB
   </code>
   table where
   <code class="literal">
    a
   </code>
   is an
        auto-increment column, the
   <code class="literal">
    INSERT
   </code>
   statement
        increases the auto-increment value but the
   <code class="literal">
    UPDATE
   </code>
   does not.
  </p>
  <p>
   If column
   <code class="literal">
    b
   </code>
   is also unique, the
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   is equivalent to this
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statement instead:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa15446400"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">UPDATE</span> t1 <span class="token keyword">SET</span> c<span class="token operator">=</span>c<span class="token operator">+</span><span class="token number">1</span> <span class="token keyword">WHERE</span> a<span class="token operator">=</span><span class="token number">1</span> <span class="token operator">OR</span> b<span class="token operator">=</span><span class="token number">2</span> <span class="token keyword">LIMIT</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If
   <code class="literal">
    a=1 OR b=2
   </code>
   matches several rows, only
   <span class="emphasis">
    <em>
     one
    </em>
   </span>
   row is updated. In general, you should
        try to avoid using an
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   clause on tables with multiple unique indexes.
  </p>
  <p>
   With
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   , the
        affected-rows value per row is 1 if the row is inserted as a new
        row, 2 if an existing row is updated, and 0 if an existing row
        is set to its current values. If you specify the
   <code class="literal">
    CLIENT_FOUND_ROWS
   </code>
   flag to the
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-connect.html" target="_top">
    <code class="literal">
     mysql_real_connect()
    </code>
   </a>
   C API
        function when connecting to
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   , the
        affected-rows value is 1 (not 0) if an existing row is set to
        its current values.
  </p>
  <p>
   If a table contains an
   <code class="literal">
    AUTO_INCREMENT
   </code>
   column
        and
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT
        ... ON DUPLICATE KEY UPDATE
    </code>
   </a>
   inserts or updates a row,
        the
   <a class="link" href="information-functions.html#function_last-insert-id">
    <code class="literal">
     LAST_INSERT_ID()
    </code>
   </a>
   function
        returns the
   <code class="literal">
    AUTO_INCREMENT
   </code>
   value.
  </p>
  <p>
   The
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   clause can
        contain multiple column assignments, separated by commas.
  </p>
  <p>
   It is possible to use
   <code class="literal">
    IGNORE
   </code>
   with
   <code class="literal">
    ON
        DUPLICATE KEY UPDATE
   </code>
   in an
   <code class="literal">
    INSERT
   </code>
   statement, but this may not behave as you expect when inserting
        multiple rows into a table that has multiple unique keys. This
        becomes apparent when an updated value is itself a duplicate key
        value. Consider the table
   <code class="literal">
    t
   </code>
   , created and
        populated by the statements shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa44163582"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t <span class="token punctuation">(</span>a <span class="token keyword">SERIAL</span><span class="token punctuation">,</span> b <span class="token datatype">BIGINT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>b<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.03 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected (0.01 sec)</span>
<span class="token output">Records: 2  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> t<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a <span class="token punctuation">|</span> b <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span> 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> 2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Now we attempt to insert two rows, one of which contains a
        duplicate key value, using
   <code class="literal">
    ON DUPLICATE KEY
        UPDATE
   </code>
   , where the
   <code class="literal">
    UPDATE
   </code>
   clause
        itself results in a duplicate key value:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa41392532"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> a<span class="token operator">=</span>a<span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">,</span> b<span class="token operator">=</span>b<span class="token number">-1</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1062 (23000)<span class="token punctuation">:</span> Duplicate entry '1' for key 't.b'</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> t<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a <span class="token punctuation">|</span> b <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span> 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> 2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   The first row contains a duplicate value for one of the
        table's unique keys (column
   <code class="literal">
    a
   </code>
   ), but
   <code class="literal">
    b=b+1
   </code>
   in the
   <code class="literal">
    UPDATE
   </code>
   clause
        results in a unique key violation for column
   <code class="literal">
    b
   </code>
   ; the statement is immediately rejected with
        an error, and no rows are updated. Let us repeat the statement,
        this time adding the
   <strong class="userinput">
    <code>
     IGNORE
    </code>
   </strong>
   keyword, like
        this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa32389108"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">IGNORE</span> <span class="token keyword">INTO</span> t <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span> 
<span class="token prompt">    -&gt;</span> <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> a<span class="token operator">=</span>a<span class="token operator">+</span><span class="token number">1</span><span class="token punctuation">,</span> b<span class="token operator">=</span>b<span class="token number">-1</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected, 1 warning (0.00 sec)</span>
<span class="token output">Records: 2  Duplicates: 1  Warnings: 1</span></code></pre>
  </div>
  <p>
   This time, the previous error is demoted to a warning, as shown
        here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81096315"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">WARNINGS</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Level   <span class="token punctuation">|</span> Code <span class="token punctuation">|</span> Message                           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> Warning <span class="token punctuation">|</span> 1062 <span class="token punctuation">|</span> Duplicate entry '1' for key 't.b' <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Because the statement was not rejected, execution continues.
        This means that the second row is inserted into
   <code class="literal">
    t
   </code>
   , as we can see here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa7370420"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> t<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a <span class="token punctuation">|</span> b <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span> 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> 2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 3 <span class="token punctuation">|</span> 3 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">3 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   In assignment value expressions in the
   <code class="literal">
    ON DUPLICATE KEY
        UPDATE
   </code>
   clause, you can use the
   <a class="link" href="miscellaneous-functions.html#function_values">
    <code class="literal">
     VALUES(
     <em class="replaceable">
      <code>
       col_name
      </code>
     </em>
     )
    </code>
   </a>
   function to refer to column values from the
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   portion of the
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT ...
        ON DUPLICATE KEY UPDATE
    </code>
   </a>
   statement. In other words,
   <a class="link" href="miscellaneous-functions.html#function_values">
    <code class="literal">
     VALUES(
     <em class="replaceable">
      <code>
       col_name
      </code>
     </em>
     )
    </code>
   </a>
   in the
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   clause refers
        to the value of
   <em class="replaceable">
    <code>
     col_name
    </code>
   </em>
   that would
        be inserted, had no duplicate-key conflict occurred. This
        function is especially useful in multiple-row inserts. The
   <a class="link" href="miscellaneous-functions.html#function_values">
    <code class="literal">
     VALUES()
    </code>
   </a>
   function is meaningful
        only as an introducer for
   <code class="literal">
    INSERT
   </code>
   statement
        value lists, or in the
   <code class="literal">
    ON DUPLICATE KEY
        UPDATE
   </code>
   clause of an
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statement, and returns
   <code class="literal">
    NULL
   </code>
   otherwise. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71383506"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span>
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> c<span class="token operator">=</span><span class="token keyword">VALUES</span><span class="token punctuation">(</span>a<span class="token punctuation">)</span><span class="token operator">+</span><span class="token keyword">VALUES</span><span class="token punctuation">(</span>b<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   That statement is identical to the following two statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa66883053"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span>
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> c<span class="token operator">=</span><span class="token number">3</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span>
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> c<span class="token operator">=</span><span class="token number">9</span><span class="token punctuation">;</span></code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The use of
    <a class="link" href="miscellaneous-functions.html#function_values">
     <code class="literal">
      VALUES()
     </code>
    </a>
    to refer to
          the new row and columns is deprecated, and subject to removal
          in a future version of MySQL. Instead, use row and column
          aliases, as described in the next few paragraphs of this
          section.
   </p>
  </div>
  <p>
   It is possible to use an alias for the row, with, optionally,
        one or more of its columns to be inserted, following the
   <code class="literal">
    VALUES
   </code>
   or
   <code class="literal">
    SET
   </code>
   clause, and
        preceded by the
   <code class="literal">
    AS
   </code>
   keyword. Using the row
        alias
   <code class="literal">
    new
   </code>
   , the statement shown previously
        using
   <code class="literal">
    VALUES()
   </code>
   to access the new column
        values can be written in the form shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13369123"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">new</span>
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> c <span class="token operator">=</span> <span class="token keyword">new</span><span class="token punctuation">.</span>a<span class="token operator">+</span><span class="token keyword">new</span><span class="token punctuation">.</span>b<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If, in addition, you use the column aliases
   <code class="literal">
    m
   </code>
   ,
   <code class="literal">
    n
   </code>
   , and
   <code class="literal">
    p
   </code>
   , you can omit the row alias in the
        assignment clause and write the same statement like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa38535964"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>a<span class="token punctuation">,</span>b<span class="token punctuation">,</span>c<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">6</span><span class="token punctuation">)</span> <span class="token keyword">AS</span> <span class="token keyword">new</span><span class="token punctuation">(</span>m<span class="token punctuation">,</span>n<span class="token punctuation">,</span>p<span class="token punctuation">)</span>
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> c <span class="token operator">=</span> m<span class="token operator">+</span>n<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   When using column aliases in this fashion, you must still use a
        row alias following the
   <code class="literal">
    VALUES
   </code>
   clause, even
        if you do not make direct use of it in the assignment clause.
  </p>
  <p>
   An
   <code class="literal">
    INSERT ... SELECT ... ON DUPLICATE KEY
        UPDATE
   </code>
   statement that uses
   <code class="literal">
    VALUES()
   </code>
   in the
   <code class="literal">
    UPDATE
   </code>
   clause, like this one, throws a
        warning:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa20452088"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1
  <span class="token keyword">SELECT</span> c<span class="token punctuation">,</span> c<span class="token operator">+</span>d <span class="token keyword">FROM</span> t2
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> b <span class="token operator">=</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span>b<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can eliminate such warnings by using a subquery instead,
        like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa41828373"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1
  <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token punctuation">(</span><span class="token keyword">SELECT</span> c<span class="token punctuation">,</span> c<span class="token operator">+</span>d <span class="token keyword">AS</span> e <span class="token keyword">FROM</span> t2<span class="token punctuation">)</span> <span class="token keyword">AS</span> dt
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> b <span class="token operator">=</span> e<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You can also use row and column aliases with a
   <code class="literal">
    SET
   </code>
   clause, as mentioned previously.
        Employing
   <code class="literal">
    SET
   </code>
   instead of
   <code class="literal">
    VALUES
   </code>
   in the two
   <code class="literal">
    INSERT ... ON
        DUPLICATE KEY UPDATE
   </code>
   statements just shown can be done
        as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95135256"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">SET</span> a<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">,</span>b<span class="token operator">=</span><span class="token number">2</span><span class="token punctuation">,</span>c<span class="token operator">=</span><span class="token number">3</span> <span class="token keyword">AS</span> <span class="token keyword">new</span>
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> c <span class="token operator">=</span> <span class="token keyword">new</span><span class="token punctuation">.</span>a<span class="token operator">+</span><span class="token keyword">new</span><span class="token punctuation">.</span>b<span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token keyword">SET</span> a<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">,</span>b<span class="token operator">=</span><span class="token number">2</span><span class="token punctuation">,</span>c<span class="token operator">=</span><span class="token number">3</span> <span class="token keyword">AS</span> <span class="token keyword">new</span><span class="token punctuation">(</span>m<span class="token punctuation">,</span>n<span class="token punctuation">,</span>p<span class="token punctuation">)</span>
  <span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> c <span class="token operator">=</span> m<span class="token operator">+</span>n<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The row alias must not be the same as the name of the table. If
        column aliases are not used, or if they are the same as the
        column names, they must be distinguished using the row alias in
        the
   <code class="literal">
    ON DUPLICATE KEY UPDATE
   </code>
   clause. Column
        aliases must be unique with regard to the row alias to which
        they apply (that is, no column aliases referring to columns of
        the same row may be the same).
  </p>
  <p>
   For
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT
        ... SELECT
    </code>
   </a>
   statements, these rules apply regarding
        acceptable forms of
   <code class="literal">
    SELECT
   </code>
   query expressions
        that you can refer to in an
   <code class="literal">
    ON DUPLICATE KEY
        UPDATE
   </code>
   clause:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      References to columns from queries on a single table, which
            may be a derived table.
     </p>
    </li>
    <li class="listitem">
     <p>
      References to columns from queries on a join over multiple
            tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      References to columns from
      <code class="literal">
       DISTINCT
      </code>
      queries.
     </p>
    </li>
    <li class="listitem">
     <p>
      References to columns in other tables, as long as the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
       </code>
      </a>
      does not use
      <code class="literal">
       GROUP BY
      </code>
      . One side effect is that you
            must qualify references to nonunique column names.
     </p>
    </li>
   </ul>
  </div>
  <p>
   References to columns from a
   <a class="link" href="union.html" title="15.2.18 UNION Clause">
    <code class="literal">
     UNION
    </code>
   </a>
   are not supported. To work
        around this restriction, rewrite the
   <a class="link" href="union.html" title="15.2.18 UNION Clause">
    <code class="literal">
     UNION
    </code>
   </a>
   as a derived table so that
        its rows can be treated as a single-table result set. For
        example, this statement produces an error:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25552272"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>a<span class="token punctuation">,</span> b<span class="token punctuation">)</span>
  <span class="token keyword">SELECT</span> c<span class="token punctuation">,</span> d <span class="token keyword">FROM</span> t2
  <span class="token keyword">UNION</span>
  <span class="token keyword">SELECT</span> e<span class="token punctuation">,</span> f <span class="token keyword">FROM</span> t3
<span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> b <span class="token operator">=</span> b <span class="token operator">+</span> c<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Instead, use an equivalent statement that rewrites the
   <a class="link" href="union.html" title="15.2.18 UNION Clause">
    <code class="literal">
     UNION
    </code>
   </a>
   as a derived table:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65484287"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t1 <span class="token punctuation">(</span>a<span class="token punctuation">,</span> b<span class="token punctuation">)</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span>
  <span class="token punctuation">(</span><span class="token keyword">SELECT</span> c<span class="token punctuation">,</span> d <span class="token keyword">FROM</span> t2
   <span class="token keyword">UNION</span>
   <span class="token keyword">SELECT</span> e<span class="token punctuation">,</span> f <span class="token keyword">FROM</span> t3<span class="token punctuation">)</span> <span class="token keyword">AS</span> dt
<span class="token keyword">ON</span> <span class="token keyword">DUPLICATE</span> <span class="token keyword">KEY</span> <span class="token keyword">UPDATE</span> b <span class="token operator">=</span> b <span class="token operator">+</span> c<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The technique of rewriting a query as a derived table also
        enables references to columns from
   <code class="literal">
    GROUP BY
   </code>
   queries.
  </p>
  <p>
   Because the results of
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT ...
        SELECT
    </code>
   </a>
   statements depend on the ordering of rows from
        the
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   and this order cannot
        always be guaranteed, it is possible when logging
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT ...
        SELECT ON DUPLICATE KEY UPDATE
    </code>
   </a>
   statements for the
        source and the replica to diverge. Thus,
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT ...
        SELECT ON DUPLICATE KEY UPDATE
    </code>
   </a>
   statements are flagged
        as unsafe for statement-based replication. Such statements
        produce a warning in the error log when using statement-based
        mode and are written to the binary log using the row-based
        format when using
   <code class="literal">
    MIXED
   </code>
   mode. An
   <a class="link" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    <code class="literal">
     INSERT ...
        ON DUPLICATE KEY UPDATE
    </code>
   </a>
   statement against a table
        having more than one unique or primary key is also marked as
        unsafe. (Bug #11765650, Bug #58637)
  </p>
  <p>
   See also
   <a class="xref" href="replication-sbr-rbr.html" title="19.2.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication">
    Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based
        Replication”
   </a>
   .
  </p>
 </div>
 <br/>
</div>
