# REPLACE (See [REPLACE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/string-functions/replace.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/insert.md), simply inserting a new row into the table. ## Syntax ```sql 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-index.md) , [CREATE INDEX](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/create-index.md), or [DROP INDEX](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-definition-language-ddl/drop-index.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md)). * Writing to multiple databases in a transaction is not supported. * SingleStore recommends to use the [INSERT ON DUPLICATE KEY UPDATE](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/performing-upserts.md) statement instead of `REPLACE`, because it allows you to use more powerful expressions in the update logic. * Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions. ## Examples The examples in this section use the following `Emp` table: ```sql DESCRIBE Emp; ``` ```output +-------+----------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+------+---------+-------+ | ID | int(11) | NO | PRI | NULL | | | Name | char(20) | YES | | NULL | | | City | char(20) | YES | | NULL | | +-------+----------+------+------+---------+-------+ ``` ```sql SELECT * FROM Emp; ``` ```output +----+-------+-------------+ | 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: ```sql REPLACE INTO Emp(ID,Name,City) VALUES(10,"Bill","San Jose"); ``` ```output Query OK, 2 rows affected (0.96 sec) ``` ```sql SELECT * FROM Emp; ``` ```output +----+-------+-------------+ | 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: ```sql 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: ```sql REPLACE INTO Emp(ID,City) VALUES(10,"San Jose"); SELECT * FROM Emp; ``` ```output +----+-------+-------------+ | 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: ```sql SELECT * FROM EmpTable; ``` ```output +----+-------+-------------+ | ID | Name | City | +----+-------+-------------+ | 10 | Bill | San Jose | | 20 | Clory | Austin | +----+-------+-------------+ ``` ```sql SELECT * FROM EmpCity; ``` ```output +----+---------+-----------+ | 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: ```sql REPLACE INTO EmpTable(ID,Name,City) SELECT ID,EmpName,CityName FROM EmpCity WHERE ID = 20; SELECT * FROM EmpTable; ``` ```output +----+-------+-------------+ | ID | Name | City | +----+-------+-------------+ | 10 | Bill | San Jose | | 20 | Teresa| Baltimore | +----+-------+-------------+ ``` ## Related Topics * [INSERT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/insert.md) * [LOAD DATA](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/load-data.md) *** Modified at: January 22, 2026 Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/replace/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/replace/) (An index of the documentation is available at /llms.txt)