# UPDATE `UPDATE` is a DML statement that modifies rows in a table. ## Syntax ```sql UPDATE table_references [[LEFT|RIGHT] JOIN [table valued expression] [ON join_condition]] SET column_reference=expr [ , column_reference=expr , ... ] [WHERE where_condition] [LIMIT row_count]; ``` For information on the `RETURNING` clause refer [UPDATE ... RETURNING](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/update-returning.md) ## Arguments **table\_references** One or more tables to reference during the update operation. Refer to the [SELECT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/select.md) statement documentation for full definition of `table_references`. The `table_references` clause may be a comma-separated list of tables, or a join expression. A single table must be the target of the update operation. The target fields must either be identified by two part names (`table.field`), on the left of assignments in the SET clause, or single identifiers. If single identifiers are used, the target table is assumed to be the leftmost table in the `table_references` clause. **column\_reference** A column in the specified target table to update, designated by either a single identifier or `table.field`. **expr** An expression that evaluates to a valid column-type value for the specified column. **where\_condition** One or more expressions that evaluate to true for each row to update. **row\_count** The maximum number of rows to be updated. ## Remarks The `UPDATE` statement modifies each specified field in a row with new values. Its `SET` clause indicates which columns to modify and the values they should be given. Each value can be specified as an expression. If specified, the `WHERE` clause provides any conditions that identify which rows to update. If a `WHERE` clause is not specified, all rows are updated. Finally, the `LIMIT` clause places a limit on the number of rows that can be updated. Although `UPDATE` supports referencing multiple tables using either join or subquery, SingleStore only supports changing one table in `UPDATE` statement. In the `SET` clause, all columns must come from the target table only. The first field in the target table of type `TIMESTAMP` or `TIMESTAMP(6)` has special behavior for update operations, where it is updated to the current timestamp value when not explicitly updated with a different value. Refer to the discussion of these types in the [Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) topic for more details. In addition, a field `f` declared with the `ON UPDATE ` modifier will be updated to `` if any other field is updated, but `f` itself is not updated. `UPDATE` queries will fail if the `maximum_table_memory` limit has been reached. This command must be run on the master aggregator or a child aggregator node. For more information, see [Node Requirements for SingleStore Commands](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md). Updating columns which are part of the SHARD key is unsupported. Transactions that span more than one database are not supported. An attempt to write to multiple databases within a single transaction fails with the following error: `Feature 'Cross-database transactions is not supported by SingleStore.` SingleStore does not currently support updating a reference table based on a join with a sharded table and returns the following error: `ERROR 1706 (HY000): Feature 'Multi-table UPDATE/DELETE with a reference table as target table' is not supported by MemSQL`. This is because reference tables can only be updated on the master aggregator while joins against sharded tables must run on the leaves. For example, with `t1` as a reference table, the following update will return an error: ```sql UPDATE t1 JOIN t2 ON t1.b = t2.b SET t1.b = t1.b+1; ``` To avoid getting an error: * Use the same type of table for update. For example, use both the target table and joined table either as a sharded table or as a reference table. * Use `UPSERT` with a reference table that has a primary key. For example, if `t1` is a reference table with a primary key, then the following query runs successfully: ```sql INSERT INTO t1 SELECT t1.* FROM t1 JOIN t2 ON t1.b = t2.b ON DUPLICATE KEY UPDATE t1.b. ``` 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. ## Limitations on an `UPDATE/DELETE` Target Table Some restrictions affect the performance of `UPDATE/DELETE` queries: * The `UPDATE/DELETE` target table cannot be moved that is, broadcasted, reshuffled, or have a hash table built on it. * The `UPDATE/DELETE` target tablet is enforced to be the first table in the join tree. * It is not possible to seek into the target table while scanning another table. ## Examples ```sql UPDATE a SET c1 = 0; UPDATE a SET c1 = 0 WHERE c2 = 100; UPDATE a, b SET a.v = b.v WHERE a.name = b.name; UPDATE a LEFT JOIN b ON a.name = b.name SET a.v = b.v; UPDATE looooooooong as a, b SET a.v = b.v WHERE a.name = b.name; UPDATE a, b, c SET a.v = 0 WHERE a.x = b.x and b.y = c.y; UPDATE a, b, c SET a.v = c.v WHERE a.x = b.x and b.y = c.y; UPDATE b, a SET a.v = b.v WHERE a.name = b.name; UPDATE dataset SET valid = false WHERE v = (SELECT MAX(v) FROM dataset); UPDATE dataset SET valid = false WHERE name IN (SELECT * FROM invalid_names); UPDATE dataset SET v = v - (SELECT MIN(v) FROM dataset); UPDATE records a JOIN (SELECT name, COUNT(*) as count FROM samples GROUP BY name) b SET a.count = a.count + b.count WHERE a.name = b.name; ``` ## Updates Where More Than One Value Maps to a Target Row Depending on the data, some update statements may try to assign more than one value to a single target row. These updates are sometimes called *non-deterministic updates* or *non-functional updates.* Such updates are allowed, but the system only guarantees that one of the source values that maps to the target row will be chosen. Which value is chosen is not specified. ## Example Non-deterministic Update The following example shows the behavior of a non-deterministic update. ```sql CREATE TABLE t1(a int, b int); CREATE TABLE t2(b int, c int); INSERT t1 VALUES (1,2); INSERT t2 VALUES (2,3), (2,4); UPDATE t1, t2 SET t1.b = t2.c WHERE t1.b = t2.b; Rows matched: 1 Changed: 1 Warnings: 0 ``` ```output SELECT * FROM t1; +------+------+ | a | b | +------+------+ | 1 | 3 | +------+------+ ``` The target row’s b field is set to 3, but it could have been set to 4 as well. Which value will be chosen is implementation-defined and unspecified. In general, even different executions of the same command with the same data may produce different results when a non-deterministic update is specified. ## Update Multiple Columns that Contain Subqueries The following example shows how to update multiple columns that contain subqueries within one statement. ```sql CREATE TABLE t1 (a INT, b INT, c INT); CREATE TABLE t2 (a INT, b INT, c INT); ``` ```sql INSERT INTO t1 VALUES (1, 1, 0),(2, 2, 0),(3, 3, 0); INSERT INTO t2 VALUES (1, 11, 0),(2, 12, 0),(3, 13, 0); ``` The values for the `b` and `c` columns in `t1` can be updated to match the `b` and `c` column values in `t2` with the following statement: ```sql UPDATE t1 SET b = (SELECT b FROM t2 WHERE t1.a = t2.a), c = (SELECT c FROM t2 WHERE t1.a = t2.a) WHERE t1.a IN (SELECT t2.a FROM t2); ``` ## Updates Using Joins The following example uses the `UPDATE ... JOIN` statement to perform a cross-table update without joining to a subquery. ```sql SELECT * FROM product; ``` ```output +----+------+------------------------------------------------+ | ID | Tag | Description | +----+------+------------------------------------------------+ | 1 | US | {"description":[{"Name":"Ark","UID":"ghx"}]} | | 2 | UK | {"description":[{"Name":"Canon","UID":"yad"}]} | | 3 | US | {"description":[{"Name":"Peak","UID":"cmk"}]} | +----+------+------------------------------------------------+ ``` ```sql SELECT * FROM stock; ``` ```output +----+-------+----------+ | ID | P_ID | Quantity | +----+-------+----------+ | 1 | 12 | 2914 | | 2 | 32 | 3532 | | 3 | 64 | 2268 | +----+-------+----------+ ``` ```sql UPDATE stock s INNER JOIN product p ON s.ID = p.ID SET s.P_ID = p.ID; SELECT * FROM stock; ``` ```output +----+------+----------+ | ID | P_ID | Quantity | +----+------+----------+ | 1 | 1 | 2914 | | 2 | 2 | 3532 | | 3 | 3 | 2268 | +----+------+----------+ ``` ## Update Using Limit `LIMIT` is used with an `UPDATE` query to limit the number of rows that will be updated. However, for `UPDATE` to work, it must run on a single partition; otherwise, it will result in an error. ```sql CREATE TABLE lmt_exp(item_id INT, item_name VARCHAR(20)); INSERT INTO lmt_exp VALUES(1, 'widget'), (2, 'lgr widget'), (3, 'xl widget'); UPDATE lmt_exp SET item_id=2 LIMIT 1; ``` ```output ERROR 1749 (HY000): Feature 'UPDATE...LIMIT must be constrained to a single partition' is not supported by SingleStore Distributed. ``` In this example, the table was created using a shard key. ``` CREATE TABLE lmt_exp (item_id INT, item_name VARCHAR(24), SHARD KEY(item_id)); INSERT INTO lmt_exp VALUES(1, 'widget'), (2, 'lgr widget'), (3, 'xl widget'); SELECT * FROM lmt_exp; ``` ```output +---------+------------+ | item_id | item_name  | +---------+------------+ |       1 | widget     | |       2 | lgr widget | |       3 | xl widget  | +---------+------------+ ``` To constrain the `UPDATE` to a particular field, filter on a specific key of the `SHARD KEY`. ``` UPDATE lmt_exp SET item_name='med widget' WHERE item_id = 2 LIMIT 1; SELECT * FROM lmt_exp; ``` ```output +---------+------------+ | item_id | item_name  | +---------+------------+ |       1 | widget     | |       2 | med widget | |       3 | xl widget  | +---------+------------+ ``` > **⚠️ Warning**: Two items to note: a `SHARD KEY` cannot be added to a table after table creation, and you cannot use `UPDATE` on a `SHARD KEY` column. *** Modified at: February 18, 2026 Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/update/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/update/) (An index of the documentation is available at /llms.txt)