You are viewing an older version of this section. View current production version.
UPDATE
UPDATE
is a DML statement that modifies rows in a table.
Syntax
UPDATE table_references
SET column_reference=expr [ , column_reference=expr , ... ]
[WHERE where_condition]
[LIMIT row_count];
Arguments
table_references
One or more tables to reference during the update operation. Refer
to the SELECT
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, MemSQL 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 topic for more details.
In addition, a field f
declared with the ON UPDATE <value>
modifier will be updated to <value>
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 MemSQL Commands. Note that when running this command on reference tables you must connect to the master aggregator.
Updating columns which are part of the SHARD key is unsupported.
Writing to multiple databases in a transaction is not supported.
Examples
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.
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
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.