Sign inTry Free

REPLACE

On this page

If the table has a PRIMARY KEY or UNIQUE index, REPLACE will check rows for a matching value and, if a match is found, will delete the old row and replace it with the new row.

If no match is found, or if there is no PRIMARY KEY or UNIQUE index, the REPLACE command operates exactly like INSERT, simply inserting a new row into the table.

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

  • 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      |
+----+-------+-------------+

Example 1: Using the REPLACE and REPLACE .. SET Statement

The following REPLACE statement updates the value of column City where ID = 10:

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      |
+----+-------+-------------+

Note: The REPLACE statement first deletes the existing row and then replaces it with the new row. Hence, the affected row count is 2.

Alternatively, use the following REPLACE .. SET statement to perform the same task:

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

The REPLACE statement stores NULL in the columns that are not specified in the statement. For example, the following REPLACE statement stores NULL in the Name column since it is not included in the REPLACE statement:

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      |
+----+-------+-------------+

Example 2: Using the REPLACE .. SELECT Statement

The REPLACE .. SELECT statement allows you to update the rows of a table based on the query on another table or the same table.

Consider the following two tables EmpTable and EmpCity, where the ID column is the primary key:

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 |
+----+---------+-----------+

The following REPLACE statement updates the values of the Name and City columns of the EmpTable table based on the query results of the EmpCity table where ID = 20:

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

Last modified: March 8, 2024

Was this article helpful?

On this page

Was this article helpful?