REPLACE

Syntax

REPLACE [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} (expr,...),(...),...

REPLACE [INTO] tbl_name [(col_name,...)]
    SELECT ...

REPLACE [INTO] tbl_name
    SET col_name=expr, ...

Return Type

Returns a count indicating the number of rows affected, consisting of the sum of rows deleted and inserted.

Remarks

• REPLACE requires both INSERT and DELETE privileges for the table.

• SingleStore supports constants, DEFAULT, or nullary builtins such as NOW(), RAND(), or UNIX_TIMESTAMP() for expressions (expr) for REPLACEs.

• Arithmetic expressions are not currently supported in the VALUES clause.

• REPLACE queries will fail if maximum_table_memory limit has been reached.

• REPLACE queries will fail if there are concurrent CREATE INDEX , CREATE INDEX, or DROP INDEX statements running on the table.

• This command must be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStore Commands).

  Note that when running this command on reference tables you must connect to the master aggregator.

• Writing to multiple databases in a transaction is not supported.

• SingleStore recommends to use the INSERT ON DUPLICATE KEY UPDATE statement instead of REPLACE, because it allows you to use more powerful expressions in the update logic.

• Refer to the Permission Matrix for the required permission.

Examples

The examples in this section use the following Emp table:

DESCRIBE Emp;

+-------+----------+------+------+---------+-------+
| Field | Type     | Null | Key  | Default | Extra |
+-------+----------+------+------+---------+-------+
| ID    | int(11)  | NO   | PRI  | NULL    |       |
| Name  | char(20) | YES  |      | NULL    |       |
| City  | char(20) | YES  |      | NULL    |       |
+-------+----------+------+------+---------+-------+

SELECT * FROM Emp;

+----+-------+-------------+
| ID | Name  | City        |
+----+-------+-------------+
| 10 | Bill  | Chicago     |
| 20 | Clory | Houston     |
| 30 | Trace | Los Angeles |
| 40 | Rick  | Dallas      |
+----+-------+-------------+

REPLACE INTO Emp(ID,Name,City) VALUES(10,"Bill","San Jose");

Query OK, 2 rows affected (0.96 sec)

SELECT * FROM Emp;

+----+-------+-------------+
| ID | Name  | City        |
+----+-------+-------------+
| 10 | Bill  | San Jose    |
| 20 | Clory | Houston     |
| 30 | Trace | Los Angeles |
| 40 | Rick  | Dallas      |
+----+-------+-------------+

REPLACE INTO Emp SET ID = 10, Name = "Bill", City = "San Jose";

REPLACE INTO Emp(ID,City) VALUES(10,"San Jose");

SELECT * FROM Emp;

+----+-------+-------------+
| ID | Name  | City        |
+----+-------+-------------+
| 10 | NULL  | San Jose    |
| 20 | Clory | Houston     |
| 30 | Trace | Los Angeles |
| 40 | Rick  | Dallas      |
+----+-------+-------------+

SELECT * FROM EmpTable;

+----+-------+-------------+
| ID | Name  | City        |
+----+-------+-------------+
| 10 | Bill  | San Jose    |
| 20 | Clory | Austin      |
+----+-------+-------------+

SELECT * FROM EmpCity;

+----+---------+-----------+
| ID | EmpName | CityName  |
+----+---------+-----------+
| 10 | Bill    | San Jose  |
| 20 | Teresa  | Baltimore |
+----+---------+-----------+

REPLACE INTO EmpTable(ID,Name,City) SELECT ID,EmpName,CityName FROM EmpCity WHERE ID = 20;

SELECT * FROM EmpTable;

+----+-------+-------------+
| ID | Name  | City        |
+----+-------+-------------+
| 10 | Bill  | San Jose    |
| 20 | Teresa| Baltimore   |
+----+-------+-------------+

Related Topics

• INSERT

• LOAD DATA