(mysql.info.gz) REPLACE

Info Catalog

(mysql.info.gz) LOAD DATA

(mysql.info.gz) Data Manipulation

(mysql.info.gz) SELECT

 
 13.1.6 `REPLACE' Syntax
 -----------------------
 
      REPLACE [LOW_PRIORITY | DELAYED]
          [INTO] TBL_NAME [(COL_NAME,...)]
          VALUES ({EXPR | DEFAULT},...),(...),...
 Or:
      REPLACE [LOW_PRIORITY | DELAYED]
          [INTO] TBL_NAME
          SET COL_NAME={EXPR | DEFAULT}, ...
 Or:
      REPLACE [LOW_PRIORITY | DELAYED]
          [INTO] TBL_NAME [(COL_NAME,...)]
          SELECT ...
 
 `REPLACE' works exactly like `INSERT', except that if an old record in
 the table has the same value as a new record for a `PRIMARY KEY' or a
 `UNIQUE' index, the old record is deleted before the new record is
 inserted.   `INSERT' INSERT.
 
 Note that unless the table has a `PRIMARY KEY' or `UNIQUE' index, using
 a `REPLACE' statement makes no sense. It becomes equivalent to
 `INSERT', because there is no index to be used to determine whether a
 new row duplicates another.
 
 Values for all columns are taken from the values specified in the
 `REPLACE' statement.  Any missing columns are set to their default
 values, just as happens for `INSERT'.  You can't refer to values from
 the old row and use them in the new row.  It appeared that you could do
 this in some old MySQL versions, but that was a bug that has been
 corrected.
 
 To be able to use `REPLACE', you must have `INSERT' and `DELETE'
 privileges for the table.
 
 The `REPLACE' 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 `REPLACE', 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.
 
 The affected-rows count makes it easy to determine whether `REPLACE'
 only added a row or whether it also replaced any rows: Check whether the
 count is 1 (added) or greater (replaced).
 
 If you are using the C API, the affected-rows count can be obtained
 using the `mysql_affected_rows()' function.
 
 Currently, you cannot replace into a table and select from the same
 table in a subquery.
 
 Here follows in more detail the algorithm that is used (it is also used
 with `LOAD DATA ... REPLACE'):
 
   1. Try to insert the new row into the table
 
   2. While the insertion fails because a duplicate-key error occurs for
      a primary or unique key:
        1. Delete from the table the conflicting row that has the
           duplicate key value
 
        2. Try again to insert the new row into the table
 

Info Catalog

(mysql.info.gz) LOAD DATA

(mysql.info.gz) Data Manipulation

(mysql.info.gz) SELECT