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 ...
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
:
AUTO_INCREMENT
field, a new value will be generated. ON DELETE
action will be activated by REPLACE
. 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.
DELAYED
clause
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/replace/