UPDATE

更新 Primary Key 表中的行。

StarRocks 自 v2.3 版本起支持 UPDATE 语句，但仅支持单表 UPDATE，不支持公共表表达式 (CTE)。自 3.0 版本起，StarRocks 丰富了语法以支持多表 Join 和 CTE。如果您需要将要更新的表与数据库中的其他表连接，可以在 FROM 子句或 CTE 中引用这些其他表。从 3.1 版本开始，UPDATE 语句支持列模式下的部分更新，这适用于涉及少量列但大量行的场景，从而提高更新速度。

此命令需要您拥有要更新的表的 UPDATE 权限。

使用说明

当执行涉及多表的 UPDATE 语句时，StarRocks 会将 UPDATE 语句 FROM 子句中的表表达式转换为等效的 JOIN 查询语句。因此，请确保您在 UPDATE 语句的 FROM 子句中指定的表表达式支持此转换。例如，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 查询的结果集匹配要更新的数据行。结果集中的多行可能与要更新的表中的某一行匹配。在这种情况下，该行将基于这些多行中随机行的值进行更新。

您可以使用系统变量 insert_timeout 设置 UPDATE 操作的超时时间。

语法

单表 UPDATE

如果要更新的表的数据行满足 WHERE 条件，则这些数据行的指定列将被赋予新值。

[ WITH <with_query> [, ...] ]
UPDATE <table_name>
    SET <column_name> = <expression> [, ...]
    WHERE <where_condition>

多表 UPDATE

多表 Join 的结果集与要更新的表进行匹配。 如果要更新的表的数据行与结果集匹配并满足 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 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”。 但是，对于列模式下的部分更新，此参数不是必需的。

列模式下的部分更新（自 v3.1 起）

列模式下的部分更新适用于只需要更新少量列但大量行的场景。 在这种场景下，启用列模式可以提高更新速度。 例如，在一个包含 100 列的表中，如果只更新所有行的 10 列（总数的 10%），则列模式的更新速度会快 10 倍。

系统变量 partial_update_mode 控制部分更新的模式，支持以下值

• auto（默认）：系统通过分析 UPDATE 语句和涉及的列自动确定部分更新的模式。 如果满足以下标准，系统会自动使用列模式

  • 更新的列占总列数的百分比小于 30%，并且更新的列数少于 4 个。
  • UPDATE 语句不使用 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  -- Increase the salary by 10%.
WHERE true;

如果您需要给工资低于平均工资的员工加薪 10%，您可以执行以下语句

UPDATE Employees
SET Salary = Salary * 1.1   -- Increase the salary by 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   -- Increase the salary by 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  -- Increase the salary by 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 -- Increase the salary by 10%.
FROM Acme_Accounts
WHERE Employees.Name = Acme_Accounts.Sales_person;