REPLACE

Syntax

Or:

Or:

Description

REPLACE works exactly like , except that if an old row in the table has the same value as a new row for a PRIMARY KEY or aUNIQUE 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 ). This allows to use to copy rows between different databases.

Basically it works like this:

The above can be replaced with:

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 and .

Note that unless the table has a PRIMARY KEY orUNIQUE 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. See for details. Specified in theREPLACE 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 asDEFAULT(col), so the assignment is equivalent to'SET col = DEFAULT(col) + 1'.

To use REPLACE, you must have both theINSERT and DELETE for the table.

There are some issues you should be aware of, before using REPLACE:

• If there is an field, a new value will be generated.

• If there are foreign keys, ON DELETE action will be activated by REPLACE.

• on DELETE and INSERT will be activated by REPLACE

To avoid some of these behaviors, you can use INSERT ... ON DUPLICATE KEY UPDATE.

This statement activates INSERT and DELETE triggers. See for details.

PARTITION

See for details.

REPLACE RETURNING

REPLACE ... RETURNING returns a resultset of the replaced rows. This returns the listed columns for all the rows that are replaced, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.

Examples

Simple REPLACE statement:

Using stored functions in RETURNING:

Subqueries in the statement:

Subqueries in the RETURNING clause that return more than one row or column cannot be used..

Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used, or it can be used in REPLACE...SELECT.

REPLACE ... RETURNING returns a result set of the replaced rows. This returns the listed columns for all the rows that are replaced, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for the RETURNING clause, including virtual columns and aliases, expressions which use various operators such as bitwise, logical and arithmetic operators, string functions, date-time functions, numeric functions, control flow functions, secondary functions and stored functions. Along with this, statements which have subqueries and prepared statements can also be used.

Examples

Simple REPLACE statement:

Using stored functions in RETURNING:

Subqueries in the statement:

Subqueries in the RETURNING clause that return more than one row or column cannot be used..

Aggregate functions cannot be used in the RETURNING clause. Since aggregate functions work on a set of values and if the purpose is to get the row count, ROW_COUNT() with SELECT can be used, or it can be used in REPLACE...SELECT...RETURNING if the table in the RETURNING clause is not the same as the REPLACE table. SELECT...RETURNING if the table in the RETURNING clause is not the same as the REPLACE table.

See Also

• for details on the DELAYED clause

_{This page is licensed: GPLv2, originally from}