REPLACE
On this page
(See REPLACE for the String function.
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_nameSET col_name=expr, ...
Return Type
Returns a count indicating the number of rows affected, consisting of the sum of rows deleted and inserted.
Remarks
-
REPLACErequires bothINSERTandDELETEprivileges for the table. -
SingleStore supports constants,
DEFAULT, or nullary builtins such asNOW(),RAND(), orUNIX_for expressions (TIMESTAMP() expr) for REPLACEs. -
Arithmetic expressions are not currently supported in the
VALUESclause. -
REPLACEqueries will fail ifmaximum_limit has been reached.table_ memory -
REPLACEqueries 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 permissions.
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 . 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.2.
Alternatively, use the following REPLACE . 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.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 . Statement
The REPLACE . 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 5, 2025