(mysql.info) replace

Info Catalog

(mysql.info) load-data

(mysql.info) data-manipulation

(mysql.info) select

 
 13.2.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 row in the
 table has the same value as a new row for a `PRIMARY KEY' or a `UNIQUE'
 index, the old row is deleted before the new row is inserted. See 
 insert.
 
 `REPLACE' is a MySQL extension to the SQL standard. It either inserts,
 or _deletes_ and inserts. If you're looking for a statement that
 follows the SQL standard, and that either inserts or _updates_, look
 for the `INSERT ...  ON DUPLICATE KEY UPDATE' statement; see 
 insert-on-duplicate.
 
 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 cannot refer to values from
 the current row and use them in the new row. If you use an assignment
 such as `SET COL_NAME = COL_NAME + 1', the reference to the column name
 on the right hand side is treated as `DEFAULT(COL_NAME)', so the
 assignment is equivalent to `SET COL_NAME = DEFAULT(COL_NAME) + 1'.
 
 To use `REPLACE', you must have both the `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.
 
 MySQL uses the following algorithm for `REPLACE' (and `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 key or unique index:
 
        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) load-data

(mysql.info) data-manipulation

(mysql.info) select