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 13.1.4 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 .)

back to MySQL reference