UPDATE
该语句用于更新一张主键表中的数据行。
2.3 ~ 2.5 版本,StarRocks 提供 UPDATE 语句,并且仅支持单表 UPDATE 且不支持公用表表达式(CTE)。从 3.0 版本开始,StarRocks 丰富了 UPDATE 语法,支持使用多表关联和 CTE。如果需要将待更新的表与数据库中其他表关联,则可以 在 FROM 子句或 CTE 中引用其他的表。从 3.1 版本开始,支持列模式的部分更新,适用于涉及少数列但是大量行的场景,加快更新速度。
使用说明
如果为多表 UPDATE,则您需要确保 UPDATE 语句中 FROM 子句的表表达式可以转换成等价的 JOIN 查询语句。因为 StarRocks 实际执行 UPDATE 语句时,内部会进行这样的转换。假设 UPDATE 语句为 UPDATE t0 SET v1=t1.v1 FROM t1 WHERE t0.pk = t1.pk;
,该 FROM 子句的表表达式可以转换为 t0 JOIN t1 ON t0.pk=t1.pk;
。并且 StarRocks 根据 JOIN 查询的结果集,匹配待更新表的数据行,更新其指定列的值。如果结果集中存在多行数据和待更新表的某一行数据相匹配,则待更新表中这行数据指定列更新后的值,是结果集多行数据中随机一行的指定列的值。
语法
单表 UPDATE
如果待更新表的数据行满足 WHERE 条件,则对该数据行的指定列赋予新值。
[ WITH <with_query> [, ...] ]
UPDATE <table_name>
SET <column_name> = <expression> [, ...]
WHERE <where_condition>
多表 UPDATE
基于多表关联查询的结果集与待更新的表进行匹配,如果待更新表的数据行匹配结果集并且满足 WHERE 条件,则对该数据行的指定列赋予新值。
[ WITH <with_query> [, ...] ]
UPDATE <table_name>
SET <column_name> = <expression> [, ...]
[ FROM <from_item> [, ...] ]
WHERE <where_condition>
参数说明
with_query
一个或多个可以在 UPDATE 语句中通过名字引用的 CTE。CTE 是一个临时结果集,可以提高复杂语句的易读性。
table_name
待更新的表的名称。
column_name
待更新的列的名称。不需要包含表名,例如 UPDATE t1 SET t1.col = 1
是不合法的。
expression
给列赋值的表达式。
from_item
引用数据库中一个或者多个其他的表。该表与待更新的表进行连接,WHERE 子句指定连接条件,最终基于连接查询的结果集给待更新的表中匹配行的列赋值。 例如 FROM 子句为 FROM t1 WHERE t0.pk = t1.pk;
,StarRocks 实际执行 UPDATE 语句时会将该 FROM 子句的表表达式会转换为 t0 JOIN t1 ON t0.pk=t1.pk;
。
where_condition
只有满足 WHERE 条件的行才会被更新。该参数为必选,防止误更新整张表。如需更新整张表,请使用 WHERE true
。如果使用列模式的部分更新,则该参数不是必选。
列模式的部分更新(自 3.1)
列模式的部分更新适用于涉及少数列并且大量行的场景。在该场景,开启列模式,更新速度更快。例如,在一个包含 100 列的表中,每次更新 10 列(占 比 10%)并更新所有行,则开启列模式,更新性能将提高 10 倍。
系统变量 partial_update_mode
用于控制部分更新的模式,支持取值为:
-
auto
(默认值),表示由系统通过分析更新语句以及其涉及的列,自动判断执行部分更新时使用的模式。如果满足如下标准,则系统自动使用列模式:- 更新的列数占所有列数的百分比小于 30%,并且更新的列数少于 4 个。
- 更新语句中没有使用 WHERE 条件。
反之,则系统不会使用列模式。
-
column
,指定使用列模式执行部分更新,比较适用于涉及少数列并且大量行的部分列更新场景。
您可以使用 EXPLAIN UPDATE xxx
查看执行部分列更新的模式。
示例
单表 UPDATE
创建表 Employees
来记录雇员信息,向表中插入五行数据。
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(50),
Salary DECIMAL(10, 2)
)
PRIMARY KEY (EmployeeID)
DISTRIBUTED BY HASH (EmployeeID)
PROPERTIES ("replication_num" = "3");
INSERT INTO Employees VALUES
(1, 'John Doe', 5000),
(2, 'Jane Smith', 6000),
(3, 'Robert Johnson', 5500),
(4, 'Emily Williams', 4500),
(5, 'Michael Brown', 7000);
如果需要对所有员工加薪 10%,则可以执行如下语句:
UPDATE Employees
SET Salary = Salary * 1.1 -- 将薪水增加10%
WHERE true;
如果需要对薪水低于平均薪水的员工加薪 10%,则可以执行如下语句,
UPDATE Employees
SET Salary = Salary * 1.1 -- 将薪水增加10%
WHERE Salary < (SELECT AVG(Salary) FROM Employees);
您也可以使用 CTE 改写上述语句,增加易读性。
WITH AvgSalary AS (
SELECT AVG(Salary) AS AverageSalary
FROM Employees
)
UPDATE Employees
SET Salary = Salary * 1.1 -- 将薪水增加10%
FROM AvgSalary
WHERE Employees.Salary < AvgSalary.AverageSalary;
多表 UPDATE
创建表 Accounts
来记录账户信息,向表中插入三行数据。
CREATE TABLE Accounts (
Accounts_id BIGINT NOT NULL,
Name VARCHAR(26) NOT NULL,
Sales_person VARCHAR(50) NOT NULL
)
PRIMARY KEY (Accounts_id)
DISTRIBUTED BY HASH (Accounts_id)
PROPERTIES ("replication_num" = "3");
INSERT INTO Accounts VALUES
(1,'Acme Corporation','John Doe'),
(2,'Acme Corporation','Robert Johnson'),
(3,'Acme Corporation','Lily Swift');
如果需要给表 Employees
中 Acme Corporation 公司管理帐户的员工加薪 10%,则可以执行如下语句:
UPDATE Employees
SET Salary = Salary * 1.1 -- 将薪水增加10%
FROM Accounts
WHERE Accounts.name = 'Acme Corporation'
AND Employees.Name = Accounts.Sales_person;
您也可以使用 CTE 改写上述语句,增加易读性。
WITH Acme_Accounts as (
SELECT * from Accounts
WHERE Accounts.name = 'Acme Corporation'
)
UPDATE Employees SET Salary = Salary * 1.1 -- 将薪水增加10%
FROM Acme_Accounts
WHERE Employees.Name = Acme_Accounts.Sales_person;