Updates data in a table.

Syntax

UPDATE tbl_name
    SET col_name1=expr [, col_name2=expr] ...
    [WHERE expr]
    [LIMIT row_count]

Remarks

• The first field 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.
• MemSQL does not currently support the full multi-table UPDATE syntax. IN or EXISTS Subqueries in the WHERE clause of an UPDATE are supported.
• UPDATE queries will fail if maximum_table_memory limit has been reached.
• This command must be run on the master aggregator or a child aggregator node (see Node Requirements for MemSQL Commands ).
• Updating columns which are part of the SHARD key is unsupported.
• Writing to multiple databases in a transaction is not supported.

Two examples of UPDATE statements are shown below:

mysql> UPDATE mytbl
SET vehicle_id='X3023X';

mysql> UPDATE mytbl
SET vehicle_id='X3023X'
WHERE vehicle_model IN (SELECT model FROM all_models);

Updates Using Joins

MemSQL does not support UPDATE statements that use joins. Workarounds are discussed below.

If the UPDATE values do not depend on other tables, for example:

UPDATE dest d JOIN source s
SET d.count = d.count + 1
WHERE d.id = s.id

then it can be rewritten as:

UPDATE dest
SET count = count + 1
WHERE dest.id IN (select id from source)

If the UPDATE values do depend on other tables, for example:

UPDATE dest d JOIN source s
SET d.name = s.name
WHERE d.id = s.id;

It can be written with a workaround taking advantage of insert select on duplicate key update, if id is a primary or unique key of dest:

INSERT INTO dest (id, name)
SELECT s.id, s.name
FROM dest d JOIN source s
WHERE d.id = s.id
ON DUPLICATE KEY UPDATE name = values(name);

Example:

CREATE TABLE dest(id bigint primary key, name text);
CREATE TABLE source(id bigint primary key, name text);

INSERT INTO dest values (1, 'old'), (2, 'old');
INSERT INTO source values (1, 'new'), (3, 'new');

INSERT INTO dest (id, name)
SELECT s.id, s.name
FROM dest d JOIN source s
WHERE d.id = s.id
ON DUPLICATE KEY UPDATE name = values(name);

SELECT * FROM dest;

+----+------+
| id | name |
+----+------+
|  1 | new  |
|  2 | old  |
+----+------+
2 rows in set (0.00 sec)

Was this Article Helpful?

Thanks For Your Feedback!