UPDATE¶

UPDATE 用于修改表中的现有记录。支持单表、多表以及 PostgreSQL 风格的 UPDATE ... SET ... FROM ... WHERE 语法。

语法描述¶

UPDATE 用于修改表中的现有记录。

语法结构¶

单表语法结构¶

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

PostgreSQL 风格 UPDATE FROM 语法¶

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

参数释义¶

UPDATE 将新值更新到指定表中现有行的列中。
SET 从句指出要修改哪些列以及它们应该被赋予的值。每个值可以作为表达式给出，或者通过 DEFAULT 明确将列设置为默认值。
WHERE 从句，用于指定用于标识要更新哪些行的条件。若无 WHERE 从句，则更新所有行。
ORDER BY 从句，指按照指定的顺序更新行。
LIMIT 从句用于限制可更新的行数。
PostgreSQL 风格的 FROM 从句引入额外的只读连接源。目标表被更新，FROM 从句中的表作为连接源使用且不会被修改。ORDER BY 和 LIMIT 不支持与 FROM 语法共用。

示例¶

单表示例

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 |
+------+------+

多表示例

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 |
+------+

支持多表 JOIN 语句。

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 风格 UPDATE FROM 示例

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');

-- 基本 PostgreSQL 风格 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 结合 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 结合 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;

UPDATE FROM 高级特性¶

FROM 子句中的多表：

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;

生成列保护：通过 UPDATE FROM 直接写入存储生成列会被拒绝。更新基列会重新计算生成列。

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;

-- 更新基列会重新计算生成列
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;

自连接：目标和源可以是同一张表。

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;

重复源行去重：当多个源行匹配一个目标行时，系统会去重选取一个完整的源行。它不会从不同源行的逐列聚合中合成新行。

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：与 PostgreSQL 风格 FROM 语法一起使用时，ORDER BY 和 LIMIT 在解析阶段被拒绝。

-- 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;