Syntax
------ 
REPLACE [LOW_PRIORITY | DELAYED]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
 
Or:
 
REPLACE [LOW_PRIORITY | DELAYED]
 [INTO] tbl_name [PARTITION (partition_list)]
 SET col={expr | DEFAULT}, ...
 
Or:
 
REPLACE [LOW_PRIORITY | DELAYED]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 SELECT ...
 
Description
----------- 
 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. If the table has more than one UNIQUE keys, it is
possible that the new row conflicts with more than one row.
In this case, all conflicting rows will be deleted.
 
The table name can be specified in the form db_name.tbl_name
or, if a default database is selected, in the form tbl_name
(see Identifier Qualifiers). This allows to use REPLACE ...
SELECT to copy rows between different databases.
 
Basically it works like this:
 
BEGIN;
SELECT 1 FROM t1 WHERE key=# FOR UPDATE;
IF found-row
 DELETE FROM t1 WHERE key=# ;
 INSERT INTO t1 VALUES (...);
ENDIF
END;
 
The above can be replaced with:
 
REPLACE INTO t1 VALUES (...)
 
 REPLACE is a MariaDB/MySQL extension to the SQL standard.
It
 either inserts, or deletes and inserts. For other
MariaDB/MySQL extensions to
 standard SQL --- that also handle duplicate values --- see
IGNORE and INSERT ON DUPLICATE KEY UPDATE.
 
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 = col + 1', the
reference to the column name on the right hand side is
treated as
 DEFAULT(col), so the assignment is equivalent to
 'SET col = DEFAULT(col) + 1'.
 
To use REPLACE, you must have both the
 INSERT and DELETE privileges
for the table.
 
There are some gotchas you should be aware of, before using
REPLACE:
If there is an AUTO_INCREMENT field, a new value will be
generated.
If there are foreign keys, ON DELETE action will be
activated by REPLACE.
Triggers on DELETE and INSERT will be activated by REPLACE.
 
To avoid some of these behaviors, you can use INSERT ... ON
DUPLICATE KEY UPDATE.
 
The PARTITION clause was introduced in MariaDB 10.0. See
Partition Pruning and Selection for details.
 
This statement activates INSERT and DELETE triggers. See
Trigger Overview for details.
 


URL: https://mariadb.com/kb/en/replace/