<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="replace">
      </a>
      15.2.12 REPLACE Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045181156480">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96506151"><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">REPLACE</span> <span class="token punctuation">[</span><span class="token keyword">LOW_PRIORITY</span> <span class="token operator">|</span> <span class="token keyword">DELAYED</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">INTO</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_name</em><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><em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</em><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 keyword">VALUES</span> <span class="token operator">|</span> <span class="token keyword">VALUE</span>} <span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><em class="replaceable">value_list</em><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 operator">|</span>
      <span class="token keyword">VALUES</span> <em class="replaceable">row_constructor_list</em>
    }

<span class="token keyword">REPLACE</span> <span class="token punctuation">[</span><span class="token keyword">LOW_PRIORITY</span> <span class="token operator">|</span> <span class="token keyword">DELAYED</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">INTO</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_name</em><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 keyword">SET</span> <em class="replaceable">assignment_list</em>

<span class="token keyword">REPLACE</span> <span class="token punctuation">[</span><span class="token keyword">LOW_PRIORITY</span> <span class="token operator">|</span> <span class="token keyword">DELAYED</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">INTO</span><span class="token punctuation">]</span> <em class="replaceable">tbl_name</em>
    <span class="token punctuation">[</span><span class="token keyword">PARTITION</span> <span class="token punctuation">(</span><em class="replaceable">partition_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">partition_name</em><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><em class="replaceable">col_name</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">col_name</em><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 keyword">SELECT</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token operator">|</span> <span class="token keyword">TABLE</span> <span class="token keyword"><em class="replaceable">table_name</em></span>}

<span class="token keyword"><em class="replaceable">value</em></span>:
    {<em class="replaceable">expr</em> <span class="token operator">|</span> <span class="token keyword">DEFAULT</span>}

<em class="replaceable">value_list</em>:
    <span class="token keyword"><em class="replaceable">value</em></span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">row_constructor_list</em>:
    <span class="token keyword">ROW</span><span class="token punctuation">(</span><em class="replaceable">value_list</em><span class="token punctuation">)</span><span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><em class="replaceable">value_list</em><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>

<em class="replaceable">assignment</em>:
    <em class="replaceable">col_name</em> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span>

<em class="replaceable">assignment_list</em>:
    <em class="replaceable">assignment</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">assignment</em><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></code></pre>
  </div>
  <p>
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   works exactly like
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   , except that if an old row
      in the table has the same value as a new row for a
   <code class="literal">
    PRIMARY KEY
   </code>
   or a
   <code class="literal">
    UNIQUE
   </code>
   index, the old row is deleted before the new row is inserted. See
   <a class="xref" href="insert.html" title="15.2.7 INSERT Statement">
    Section 15.2.7, “INSERT Statement”
   </a>
   .
  </p>
  <p>
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   is a MySQL extension to the
      SQL standard. It either inserts, or
   <span class="emphasis">
    <em>
     deletes
    </em>
   </span>
   and inserts. For another MySQL extension to standard
      SQL—that either inserts or
   <span class="emphasis">
    <em>
     updates
    </em>
   </span>
   —see
   <a class="xref" href="insert-on-duplicate.html" title="15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement">
    Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    DELAYED
   </code>
   inserts and replaces were deprecated in
      MySQL 5.6. In MySQL 8.4,
   <code class="literal">
    DELAYED
   </code>
   is not supported. The server recognizes but ignores the
   <code class="literal">
    DELAYED
   </code>
   keyword, handles the replace as a
      nondelayed replace, and generates an
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_warn_legacy_syntax_converted" target="_top">
    <code class="literal">
     ER_WARN_LEGACY_SYNTAX_CONVERTED
    </code>
   </a>
   warning:
   <span class="errortext">
    REPLACE DELAYED is no longer supported. The
      statement was converted to REPLACE
   </span>
   . The
   <code class="literal">
    DELAYED
   </code>
   keyword is scheduled for removal in a
      future release. release.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
     <code class="literal">
      REPLACE
     </code>
    </a>
    makes sense only if a
        table has a
    <code class="literal">
     PRIMARY KEY
    </code>
    or
    <code class="literal">
     UNIQUE
    </code>
    index. Otherwise, it becomes
        equivalent to
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    , because
        there is no index to be used to determine whether a new row
        duplicates another.
   </p>
  </div>
  <p>
   Values for all columns are taken from the values specified in the
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   statement. Any missing
      columns are set to their default values, just as happens for
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   . You cannot refer to values
      from the current row and use them in the new row. If you use an
      assignment such as
   <code class="literal">
    SET
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    =
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    + 1
   </code>
   , the reference
      to the column name on the right hand side is treated as
   <a class="link" href="miscellaneous-functions.html#function_default">
    <code class="literal">
     DEFAULT(
     <em class="replaceable">
      <code>
       col_name
      </code>
     </em>
     )
    </code>
   </a>
   ,
      so the assignment is equivalent to
   <code class="literal">
    SET
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    =
      DEFAULT(
    <em class="replaceable">
     <code>
      col_name
     </code>
    </em>
    ) + 1
   </code>
   .
  </p>
  <p>
   You can specify the column values that
   <code class="literal">
    REPLACE
   </code>
   attempts to insert using
   <a class="link" href="values.html" title="15.2.19 VALUES Statement">
    <code class="literal">
     VALUES ROW()
    </code>
   </a>
   .
  </p>
  <p>
   To use
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   , you must have both
      the
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   and
   <a class="link" href="privileges-provided.html#priv_delete">
    <code class="literal">
     DELETE
    </code>
   </a>
   privileges for the table.
  </p>
  <a class="indexterm" name="idm46045181103648">
  </a>
  <p>
   If a generated column is replaced explicitly, the only permitted
      value is
   <code class="literal">
    DEFAULT
   </code>
   . For information about
      generated columns, see
   <a class="xref" href="create-table-generated-columns.html" title="15.1.20.8 CREATE TABLE and Generated Columns">
    Section 15.1.20.8, “CREATE TABLE and Generated Columns”
   </a>
   .
  </p>
  <p>
   <code class="literal">
    REPLACE
   </code>
   supports explicit partition selection
      using the
   <code class="literal">
    PARTITION
   </code>
   clause with a list of
      comma-separated names of partitions, subpartitions, or both. As
      with
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   , if it is not possible
      to insert the new row into any of these partitions or
      subpartitions, the
   <code class="literal">
    REPLACE
   </code>
   statement fails with
      the error
   <span class="errortext">
    Found a row not matching the given partition
      set
   </span>
   . For more information and examples, see
   <a class="xref" href="partitioning-selection.html" title="26.5 Partition Selection">
    Section 26.5, “Partition Selection”
   </a>
   .
  </p>
  <p>
   The
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   statement returns a
      count to indicate the number of rows affected. This is the sum of
      the rows deleted and inserted. If the count is 1 for a single-row
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   , a row was inserted and no
      rows were deleted. If the count is greater than 1, one or more old
      rows were deleted before the new row was inserted. It is possible
      for a single row to replace more than one old row if the table
      contains multiple unique indexes and the new row duplicates values
      for different old rows in different unique indexes.
  </p>
  <p>
   The affected-rows count makes it easy to determine whether
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   only added a row or whether
      it also replaced any rows: Check whether the count is 1 (added) or
      greater (replaced).
  </p>
  <p>
   If you are using the C API, the affected-rows count can be
      obtained using the
   <a class="ulink" href="/doc/c-api/8.4/en/mysql-affected-rows.html" target="_top">
    <code class="literal">
     mysql_affected_rows()
    </code>
   </a>
   function.
  </p>
  <p>
   You cannot replace into a table and select from the same table in
      a subquery.
  </p>
  <p>
   MySQL uses the following algorithm for
   <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
    <code class="literal">
     REPLACE
    </code>
   </a>
   (and
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA ...
      REPLACE
    </code>
   </a>
   ):
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Try to insert the new row into the table
     </p>
    </li>
    <li class="listitem">
     <p>
      While the insertion fails because a duplicate-key error occurs
          for a primary key or unique index:
     </p>
     <div class="orderedlist">
      <ol class="orderedlist" type="a">
       <li class="listitem">
        <p>
         Delete from the table the conflicting row that has the
              duplicate key value
        </p>
       </li>
       <li class="listitem">
        <p>
         Try again to insert the new row into the table
        </p>
       </li>
      </ol>
     </div>
    </li>
   </ol>
  </div>
  <p>
   It is possible that in the case of a duplicate-key error, a
      storage engine may perform the
   <code class="literal">
    REPLACE
   </code>
   as an
      update rather than a delete plus insert, but the semantics are the
      same. There are no user-visible effects other than a possible
      difference in how the storage engine increments
   <code class="literal">
    Handler_
    <em class="replaceable">
     <code>
      xxx
     </code>
    </em>
   </code>
   status
      variables.
  </p>
  <p>
   Because the results of
   <code class="literal">
    REPLACE ... SELECT
   </code>
   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 these statements for
      the source and the replica to diverge. For this reason,
   <code class="literal">
    REPLACE ... SELECT
   </code>
   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. 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>
  <p>
   MySQL 8.4 supports
   <a class="link" href="table.html" title="15.2.16 TABLE Statement">
    <code class="literal">
     TABLE
    </code>
   </a>
   as well as
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   with
   <code class="literal">
    REPLACE
   </code>
   , just as it does with
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   . See
   <a class="xref" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    Section 15.2.7.1, “INSERT ... SELECT Statement”
   </a>
   , for more information and
      examples.
  </p>
  <p>
   When modifying an existing table that is not partitioned to
      accommodate partitioning, or, when modifying the partitioning of
      an already partitioned table, you may consider altering the
      table's primary key (see
   <a class="xref" href="partitioning-limitations-partitioning-keys-unique-keys.html" title="26.6.1 Partitioning Keys, Primary Keys, and Unique Keys">
    Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”
   </a>
   ).
      You should be aware that, if you do this, the results of
   <code class="literal">
    REPLACE
   </code>
   statements may be affected, just as
      they would be if you modified the primary key of a nonpartitioned
      table. Consider the table created by the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa22372664"><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">CREATE</span> <span class="token keyword">TABLE</span> test <span class="token punctuation">(</span>
  id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
  <span class="token keyword">data</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  ts <span class="token datatype">TIMESTAMP</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
  <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   When we create this table and run the statements shown in the
      mysql client, the result is as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87980893"><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">REPLACE</span> <span class="token keyword">INTO</span> test <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 string">'Old'</span><span class="token punctuation">,</span> <span class="token string">'2014-08-20 18:47:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.04 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">REPLACE</span> <span class="token keyword">INTO</span> test <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 string">'New'</span><span class="token punctuation">,</span> <span class="token string">'2014-08-20 18:47:42'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 2 rows affected (0.04 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test<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>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> data <span class="token punctuation">|</span> ts                  <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>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span> New  <span class="token punctuation">|</span> 2014<span class="token punctuation">-</span>08<span class="token punctuation">-</span>20 18:47:42 <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>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Now we create a second table almost identical to the first, except
      that the primary key now covers 2 columns, as shown here
      (emphasized text):
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa3815161"><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">CREATE</span> <span class="token keyword">TABLE</span> test2 <span class="token punctuation">(</span>
  id <span class="token datatype">INT</span> <span class="token keyword">UNSIGNED</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
  <span class="token keyword">data</span> <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">64</span><span class="token punctuation">)</span> <span class="token keyword">DEFAULT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  ts <span class="token datatype">TIMESTAMP</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">CURRENT_TIMESTAMP</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CURRENT_TIMESTAMP</span><span class="token punctuation">,</span>
  <span class="token keyword"></span><em><span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>id<span class="token punctuation">,</span> ts<span class="token punctuation">)</span></em><span class="token punctuation"></span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   When we run on
   <code class="literal">
    test2
   </code>
   the same two
   <code class="literal">
    REPLACE
   </code>
   statements as we did on the original
   <code class="literal">
    test
   </code>
   table, we obtain a different result:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11983085"><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">REPLACE</span> <span class="token keyword">INTO</span> test2 <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 string">'Old'</span><span class="token punctuation">,</span> <span class="token string">'2014-08-20 18:47:00'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.05 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">REPLACE</span> <span class="token keyword">INTO</span> test2 <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 string">'New'</span><span class="token punctuation">,</span> <span class="token string">'2014-08-20 18:47:42'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.06 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> test2<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>
<span class="token output"><span class="token punctuation">|</span> id <span class="token punctuation">|</span> data <span class="token punctuation">|</span> ts                  <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>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span> Old  <span class="token punctuation">|</span> 2014<span class="token punctuation">-</span>08<span class="token punctuation">-</span>20 18:47:00 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span>  1 <span class="token punctuation">|</span> New  <span class="token punctuation">|</span> 2014<span class="token punctuation">-</span>08<span class="token punctuation">-</span>20 18:47:42 <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>
<span class="token output">2 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   This is due to the fact that, when run on
   <code class="literal">
    test2
   </code>
   , both the
   <code class="literal">
    id
   </code>
   and
   <code class="literal">
    ts
   </code>
   column values must match those of an
      existing row for the row to be replaced; otherwise, a row is
      inserted.
  </p>
 </div>
 <br/>
</div>
