6.4.8 REPLACE Syntax

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        VALUES (expression,...),(...),...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name [(col_name,...)]
        SELECT ...
or  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] tbl_name
        SET col_name=expression, col_name=expression,...

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. See section 6.4.3 INSERT Syntax.

In other words, you can't access the values of the old row from a REPLACE statement. In some old MySQL versions it appeared that you could do this, 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.

When you use a REPLACE command, mysql_affected_rows() will return 2 if the new row replaced an old row. This is because one row was inserted after the duplicate was deleted.

This fact makes it easy to determine whether REPLACE added or replaced a row: check whether the affected-rows value is 1 (added) or 2 (replaced).

Note that unless the table has a UNIQUE index or PRIMARY KEY, using a REPLACE command makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicates another.

Here follows the used algorithm in more detail: (This is also used with LOAD DATA ... REPLACE.

- Insert the row into the table
  - While duplicate key error for primary or unique key
    - Revert changed keys
    - Read conflicting row from the table through the duplicate key value
    - Delete conflicting row
    - Try again to insert the original primary key and unique keys in the tree

User Comments

Although it is clearly stated here, it is worth
reiterating that REPLACE does not UPDATE the
existing record. When REPLACING records with an
autonumbered primary key, the autonumber column
DOES change as the old row is deleted and a new
one added.

If you use the form: {REPLACE (a, b) SELECT 1, 2},
why do you get an error where {REPLACE (a, b)
SELECT x, y FROM blah} works just fine?

Note that REPLACE will not update the information of
the row. It performs a DELETE and INSERT query.
When you excecute "REPLACE INTO table (col1,
col2) VALUES (val1, val2)" where col1 is the index,
all the other column values will be deleted. There is
not any REPLACE for UPDATE or INSERT.

Re: the first post -
If you don't want your autoincrementing field to change with a REPLACE query, create a UNIQUE INDEX which includes that field and pass the existing value for that field in the query.

Re: Re: the first post -
Or even easier (in case of bulk replace statements): temporarily switch off auto-increment by changing it into an ordinary longint (normally your auto-increment column is primary and will remain so) and then execute the replace statement - pascal van hecke (pascalvanhecke at yahoo dot com)

RE: Post above mine.

What would have the bad effect, that makes it required to care about UNIQUE IDs of rows, which do not replace an existing row, yourself, because they are not "auto_increment"ed by the DB automatically.

RE: 2nd Post above mine.
Sorry, I do not understand, what you mean.

I have use the REPLACE to add record in my program. When I edit the record in phpMyAdmin, the record is blank in edit mode. Do anyone know what happen? Also when I insert a new row by phpMyAdmin, it don't occur the problem.

In response to Thomas Karl:

If you are using AUTO_INCREMENT on a column that is your primary key, it won't replace the field and returns an error. Use the CREATE UNIQUE INDEX syntax on the same field (the auto_increment primary key field) to get around this problem, and then use REPLACE just like you would the INSERT statement.

It would be nice if REPLACE could work using compound keys as well.
For example something like
"REPLACE tablename (id, timestamp, value1, value2) VALUES (1,2,3,4) USING_KEYS (id, timestamp)"
assuming that id + timestamp together make up a unique key. A feature like this would often be useful, and right now you have to do some manual coding (first a select, then either a delete+insert or an insert).

Also a REPLACE-like command for UPDATE would be a killer command. It often happens (for example when creating apps for counting eg. hits) that you want to either A) set the value of a field to X, or B) increment/decrement the value of a field by X. Consider something like:
"if exists specified_row then update field by X
else insert row with value X"

It would be nice to write this as (strange syntax here):
"UPDATEORINSERT tablename (id=1, timestamp=2, value=value+10) USING_KEYS (id, timestamp)"

This would do an
"INSERT INTO tablename (id, timestamp, value) VALUES (1,2,10)"
if id=1 AND timestamp=2 existed, OR
"UPDATE tablename SET value=value+10 WHERE id=1 AND timestamp=2"
otherwise.

Stored procedures could be used for this later, but a more atomic command would look nicer (and I guess would also be faster).

Add your own comment.