UPDATE¶

The UPDATE statement is used to modify the existing records in a table. Supports single-table, multi-table, and PostgreSQL-style UPDATE ... SET ... FROM ... WHERE syntax.

Description¶

The UPDATE statement is used to modify the existing records in a table.

Syntax¶

Single-table Syntax¶

UPDATE table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

PostgreSQL-style UPDATE FROM Syntax¶

UPDATE table_reference [ [AS] alias ]
    SET assignment_list
    FROM table_references
    [WHERE where_condition]

Explanations¶

The UPDATE statement updates columns of existing rows in the named table with new values.
The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.
The WHERE clause, if given, specifies the conditions that identify which rows to update. With no WHERE clause, all rows are updated.
If the ORDER BY clause is specified, the rows are updated in the order that is specified.
The LIMIT clause places a limit on the number of rows that can be updated.
The PostgreSQL-style FROM clause introduces additional read-only join sources. The target table is updated; FROM-clause tables are used as join sources and are not modified. ORDER BY and LIMIT are not supported with the FROM syntax.

Examples¶

Single-table Examples

CREATE TABLE t1 (a bigint(3), b bigint(5) primary key);
insert INTO t1 VALUES (1,1),(1,2);
update t1 set a=2 where a=1 limit 1;

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
|    1 |    2 |
+------+------+

Multiple-table Examples

drop table if exists t1;
create table t1 (a int);
insert into t1 values(1), (2), (4);
drop table if exists t2;
create table t2 (b int);
insert into t2 values(1), (2), (3);
update t1, t2 set a = 1, b =2;

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
+------+

update t1, t2 set a = null, b =null;

mysql> select * from t2;
+------+
| b    |
+------+
| NULL |
| NULL |
| NULL |
+------+
mysql> select * from t1;
+------+
| a    |
+------+
| NULL |
| NULL |
| NULL |
+------+

Multiple-table join Syntax is also supported.

drop table if exists t1;
drop table if exists t2;
create table t1 (a int, b int, c int);
insert into t1 values(1, 2, 3), (4, 5, 6), (7, 8, 9);
create table t2 (a int, b int, c int);
insert into t2 values(1, 2, 3), (4, 5, 6), (7, 8, 9);
update t1 join t2 on t1.a = t2.a set t1.b = 222, t1.c = 333, t2.b = 222, t2.c = 333;

mysql> select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |  222 |  333 |
|    4 |  222 |  333 |
|    7 |  222 |  333 |
+------+------+------+

mysql> with t11 as (select * from (select * from t1) as t22) update t11 join t2 on t11.a = t2.a set t2.b = 666;

mysql> select * from t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |  666 |  333 |
|    4 |  666 |  333 |
|    7 |  666 |  333 |
+------+------+------+
3 rows in set (0.00 sec)

PostgreSQL-style UPDATE FROM Examples

DROP DATABASE IF EXISTS update_from_tests;
CREATE DATABASE update_from_tests;
USE update_from_tests;

CREATE TABLE company (id INT PRIMARY KEY, province VARCHAR(50));
INSERT INTO company VALUES (101, 'BJ'), (102, 'SH'), (103, 'GZ');

CREATE TABLE vec_join_case (id INT PRIMARY KEY, company_id INT, remark VARCHAR(100));
INSERT INTO vec_join_case VALUES (10, 101, 'init'), (20, 102, 'init'), (30, 103, 'init');

-- Basic PostgreSQL-style UPDATE FROM
UPDATE vec_join_case t
SET remark = CONCAT('hot-', c.province)
FROM company c
WHERE c.id = t.company_id;
SELECT id, company_id, remark FROM vec_join_case ORDER BY id;

-- UPDATE FROM with CTE
WITH cc AS (SELECT id, province FROM company)
UPDATE vec_join_case t
SET remark = c.province
FROM cc c
WHERE c.id = t.company_id;
SELECT id, company_id, remark FROM vec_join_case ORDER BY id;

-- UPDATE FROM with LEFT JOIN
UPDATE vec_join_case t
SET remark = COALESCE(c.province, 'unknown')
FROM company c
WHERE c.id = t.company_id;
SELECT id, company_id, remark FROM vec_join_case ORDER BY id;

DROP DATABASE update_from_tests;

Advanced UPDATE FROM Features¶

Multiple tables in FROM clause:

DROP DATABASE IF EXISTS update_from_multi;
CREATE DATABASE update_from_multi;
USE update_from_multi;

CREATE TABLE company (id INT PRIMARY KEY, province VARCHAR(50), region_id INT);
CREATE TABLE region (id INT PRIMARY KEY, name VARCHAR(20));
CREATE TABLE vec_join_case (id INT PRIMARY KEY, company_id INT, remark VARCHAR(100));

INSERT INTO company VALUES (101, 'BJ', 1), (102, 'SH', 1), (103, 'GZ', 2);
INSERT INTO region VALUES (1, 'east'), (2, 'south');
INSERT INTO vec_join_case VALUES (10, 101, 'init'), (20, 102, 'init'), (30, 103, 'init');

UPDATE vec_join_case t
SET remark = r.name
FROM company c, region r
WHERE c.id = t.company_id AND c.region_id = r.id;
SELECT id, company_id, remark FROM vec_join_case ORDER BY id;

DROP DATABASE update_from_multi;

Generated column protection: Direct writes to stored generated columns via UPDATE FROM are rejected. Updating the base column recomputes the generated column.

DROP DATABASE IF EXISTS update_from_gen;
CREATE DATABASE update_from_gen;
USE update_from_gen;

CREATE TABLE gen_t (id INT PRIMARY KEY, base INT, gen_col INT AS (base * 2) STORED);
INSERT INTO gen_t (id, base) VALUES (1, 10), (2, 20);

CREATE TABLE gen_src (id INT PRIMARY KEY, new_base INT);
INSERT INTO gen_src VALUES (1, 100), (2, 200);

-- Expected-Success: false
UPDATE gen_t SET gen_col = 999 FROM gen_src WHERE gen_src.id = gen_t.id;

-- Base column update recomputes generated column
UPDATE gen_t SET base = gen_src.new_base FROM gen_src WHERE gen_src.id = gen_t.id;
SELECT id, base, gen_col FROM gen_t ORDER BY id;

DROP DATABASE update_from_gen;

Self-join: The target and source can be the same table.

DROP DATABASE IF EXISTS update_from_self;
CREATE DATABASE update_from_self;
USE update_from_self;

CREATE TABLE sj (id INT PRIMARY KEY, parent_id INT, v VARCHAR(20));
INSERT INTO sj VALUES (1, NULL, 'root'), (2, 1, 'child2'), (3, 1, 'child3'), (4, 2, 'leaf');

UPDATE sj t SET v = p.v FROM sj p WHERE t.parent_id = p.id;
SELECT id, parent_id, v FROM sj ORDER BY id;

DROP DATABASE update_from_self;

Duplicate source row dedup: When multiple source rows match a target row, the system deduplicates to pick one whole source row. It does NOT synthesize a row from per-column aggregation of different source rows.

DROP DATABASE IF EXISTS update_from_dedup;
CREATE DATABASE update_from_dedup;
USE update_from_dedup;

CREATE TABLE whole_row_t (id INT PRIMARY KEY, a INT, b VARCHAR(20));
CREATE TABLE whole_row_s (t_id INT, new_a INT, new_b VARCHAR(20));

INSERT INTO whole_row_t VALUES (1, 0, 'orig');
INSERT INTO whole_row_s VALUES (1, NULL, 'from-null-a'), (1, 7, NULL);

UPDATE whole_row_t SET a = s.new_a, b = s.new_b FROM whole_row_s s WHERE s.t_id = whole_row_t.id;
SELECT id, a, b FROM whole_row_t;

DROP DATABASE update_from_dedup;

ORDER BY / LIMIT not allowed: ORDER BY and LIMIT are rejected at parse time when used with the PostgreSQL-style FROM syntax.

-- Expected-Success: false
UPDATE t SET v = s.v FROM s WHERE t.id = s.id ORDER BY t.id;
-- Expected-Success: false
UPDATE t SET v = s.v FROM s WHERE t.id = s.id LIMIT 1;