AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云原生大数据计算服务 MaxCompute 操作指南 开发参考：SQL SQL参考 所有命令（按字母顺序） UPDATE

UPDATE

更新时间：
产品详情
我的收藏

UPDATE操作用于将Transactional表或Delta Table表中行对应的单列或多列数据更新为新值。

前提条件

执行delete、update操作前需要具备目标Transactional表的读取表数据权限（Select）及更新表数据权限（Update）。授权操作请参见MaxCompute权限。

使用限制

  • DELETE、UPDATE功能及对应Transactional表、Delta Table表的使用限制如下：

    说明

    关于Transaction表和Delta Table表详细信息，详情请参见Transaction Table与Delta Table表参数。

    • Delta Table表UPDATE语法不支持修改PK列。

注意事项

通过DELETE、UPDATE操作删除或更新表或分区内的数据时，注意事项如下：

  • 如果需要对表中较少数据进行删除或更新操作，且操作和后续读数据的频率也不频繁，建议使用DELETE、UPDATE操作，并且在多次执行删除或更新操作之后，请合并表的Base文件和Delta文件，降低表的实际存储。更多信息，请参见合并Transactional表文件。

  • 如果删除或更新行数较多（超过5%）并且操作不频繁，但后续对该表的读操作比较频繁，建议使用INSERT OVERWRITE或INSERT INTO操作。更多信息，请参见插入或覆写数据（INSERT INTO | INSERT OVERWRITE）。

    例如，某业务场景为每次删除或更新10%的数据，一天更新10次。建议根据实际情况评估DELETE、UPDATE操作产生的费用及后续对读性能的消耗是否小于每次使用INSERT OVERWRITE或INSERT INTO操作产生的费用及后续对读性能的消耗，比较两种方式在具体场景中的效率，选择更优方案。

  • 删除数据会生成Delta文件，所以删除数据不一定能降低存储，如果您希望通过DELETE操作删除数据来降低存储，请合并表的Base文件和Delta文件，降低表的实际存储。更多信息，请参见合并Transactional表文件。

  • MaxCompute会按照批处理方式执行DELETE、UPDATE作业，每一条语句都会使用资源并产生费用，建议您使用批量方式删除或更新数据。例如您通过Python脚本生成并提交了大量行级别更新作业，且每条语句只操作一行或者少量行数据，则每条语句都会产生与SQL扫描输入数据量对应的费用，并使用相应的计算资源，多条语句累加时将明显增加费用成本，降低系统效率。命令示例如下。

    • 推荐方案：

      UPDATE table1 SET col1= (SELECT value1 FROM table2 WHERE table1.id = table2.id AND table1.region = table2.region);

    • 不推荐方案：

      UPDATE table1 SET col1=1 WHERE id='2021063001' AND region='beijing';                 
UPDATE table1 SET col1=2 WHERE id='2021063002' AND region='beijing';

命令格式

--方式1
UPDATE <table_name> [[AS] alias] SET <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];

--方式2
UPDATE <table_name> [[AS] alias] SET (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...]) [WHERE <where_condition>];

--方式3
UPDATE <table_name> [[AS] alias]
    SET <col1_name> = <value1> [, <col2_name> = <value2>, ...]
    [FROM <additional_tables>]
    [WHERE <where_condition>];

参数说明

  • table_name：必填。待执行UPDATE操作的Transactional表或Delta Table表名称。

  • alias：可选。表别名。

  • col1_name、col2_name：必填。至少更新一个。待修改行对应的列名称。

  • value1、value2：必填。至少更新一个列值。修改后的新值。

  • where_condition：可选。WHERE子句，用于筛选满足条件的数据。更多WHERE子句信息，请参见SELECT语法。如果不带WHERE子句，会更新表中的所有数据。

  • additional_tables：可选。FROM子句。

    UPDATE支持FROM子句，使用FROM子句时，UPDATE的使用会更加方便，不使用FROM子句与使用FROM子句的对比示例如下。

    场景

    示例代码

    不使用FROM子句

    UPDATE target SET v = 
  (SELECT MIN(v) FROM src GROUP BY k WHERE target.k = src.key)
  WHERE target.k IN (SELECT k FROM src);

    使用FROM子句

    UPDATE target SET v = b.v
  FROM (SELECT k, MIN(v) AS v FROM src GROUP BY k) b
  WHERE target.k = b.k;

    从上述示例代码可见：

    • 当用源表的多行数据更新目标表的一行数据的时，由于不知道用哪条源表的数据去更新，所以遇到这种情况需要用户写聚合操作来保证数据源的unique性，可以看出不使用FROM子句时代码不够简洁，使用FROM子句的写法比较简洁易懂。

    • 关联更新的时候，如果只更新交集数据，不使用FROM子句时需要写额外的where条件，相对于FROM语法而言也不太简洁。

使用示例

  • 示例1：创建非分区表acid_update，并导入数据，执行UPDATE操作更新满足指定条件的行对应的列数据。命令示例如下：

    --创建Transactional表acid_update。  
CREATE TABLE IF  NOT EXISTS acid_update(id BIGINT) tblproperties ("transactional"="true");

--插入数据。
INSERT OVERWRITE TABLE acid_update VALUES(1),(2),(3),(2);

--查看插入结果。
SELECT * FROM acid_update; 

--返回结果
+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

--将所有id为2的行，id值更新为4。
UPDATE acid_update SET id = 4 WHERE id = 2; 

--查看更新结果，2被更新为4。
SELECT * FROM acid_update; 

--返回结果
+------------+
| id         |
+------------+
| 1          |
| 3          |
| 4          |
| 4          |
+------------+

  • 示例2：创建分区表acid_update，并导入数据，执行UPDATE操作更新满足指定条件的行对应的列数据。命令示例如下：

    --创建Transactional表acid_update_pt。 
CREATE TABLE IF  NOT EXISTS acid_update_pt(id BIGINT) PARTITIONED BY(ds STRING) tblproperties ("transactional"="true");

--添加分区。
ALTER TABLE acid_update_pt ADD IF  NOT EXISTS PARTITION (ds= '2019');

--插入数据。
INSERT OVERWRITE TABLE acid_update_pt PARTITION (ds='2019') VALUES(1),(2),(3);

--查看插入结果
SELECT * FROM acid_update_pt WHERE ds = '2019'; 

--返回结果
+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+

--更新指定行的一列数据，将分区为2019的所有id=2的行，id值更新为4。
UPDATE acid_update_pt SET id = 4 WHERE ds = '2019' AND id = 2; 

--查看更新结果，2被更新为4。
SELECT * FROM acid_update_pt WHERE ds = '2019';

--返回结果
+------------+------------+
| id         | ds         |
+------------+------------+
| 4          | 2019       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

  • 示例3：创建目标表acid_update_t和关联表acid_update_s，实现同时更新多列值。命令示例如下：

    --创建待更新目标Transactional表acid_update_t和关联表acid_update_s。
CREATE TABLE IF  NOT EXISTS acid_update_t(id INT,value1 INT,value2 INT) tblproperties ("transactional"="true");
CREATE TABLE IF  NOT EXISTS acid_update_s(id INT,value1 INT,value2 INT); 

--插入数据。
INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41);
INSERT OVERWRITE TABLE acid_update_s VALUES(1,100,101),(2,200,201),(3,300,301);

--方式一：用常量更新。
UPDATE acid_update_t SET (value1, value2) = (60,61);
--查询方式一目标表结果数据。
SELECT * FROM acid_update_t;

--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 60         | 61         |
| 3          | 60         | 61         |
| 4          | 60         | 61         |
+------------+------------+------------+

--方式二：关联更新，规则为acid_update_t表左关联acid_update_s表。
UPDATE acid_update_t SET (value1, value2) = (SELECT value1, value2 FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id);
--查询方式二目标表结果数据。
SELECT * FROM acid_update_t;

--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

--方式三（基于“方式二”的结果进行更新）：关联更新，规则为增加过滤条件，只更新交集。
UPDATE acid_update_t SET (value1, value2) = (SELECT value1, value2 FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id) WHERE acid_update_t.id IN (SELECT id FROM acid_update_s);
--查询方式三目标表结果数据。
SELECT * FROM acid_update_t;

--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

--方式四（基于“方式三”的结果进行更新）：用汇总结果关联更新。
UPDATE acid_update_t SET (id, value1, value2) = (SELECT id, MAX(value1),MAX(value2) FROM acid_update_s WHERE acid_update_t.id = acid_update_s.id GROUP BY acid_update_s.id) WHERE acid_update_t.id IN (SELECT id FROM acid_update_s);
--查询方式四目标表结果数据。
SELECT * FROM acid_update_t;

--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

  • 示例4：涉及两个表的简单关联查询，示例命令如下：

    --创建更新目标表acid_update_t和关联表acid_update_s 
CREATE TABLE IF  NOT EXISTS acid_update_t(id BIGINT,value1 BIGINT,value2 BIGINT) tblproperties ("transactional"="true");
CREATE TABLE IF  NOT EXISTS acid_update_s(id BIGINT,value1 BIGINT,value2 BIGINT);

--插入数据
INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41);
INSERT OVERWRITE TABLE acid_update_s VALUES(1,100,101),(2,200,201),(3,300,301);

--查询acid_update_t表数据
SELECT * FROM acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

--查询acid_update_s表数据
SELECT * FROM acid_update_s;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

--关联更新，目标表增加过滤条件，只取交集
UPDATE acid_update_t SET value1 = b.value1, value2 = b.value2
FROM acid_update_s b WHERE acid_update_t.id = b.id;
-- 等价于上面写法
UPDATE acid_update_t a SET a.value1 = b.value1, a.value2 = b.value2 
FROM acid_update_s b WHERE a.id = b.id;

--查看更新结果，20被更新为200，21被更新为201，30被更新为300，31被更新为301
SELECT * FROM acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 4          | 40         | 41         |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

  • 示例5：涉及多个表的复杂关联查询，示例命令如下：

    --创建更新目标表acid_update_t和关联表acid_update_s 
CREATE TABLE IF  NOT EXISTS acid_update_t(id BIGINT,value1 BIGINT,value2 BIGINT) tblproperties ("transactional"="true");
CREATE TABLE IF  NOT EXISTS acid_update_s(id BIGINT,value1 BIGINT,value2 BIGINT);
CREATE TABLE IF  NOT EXISTS acid_update_m(id BIGINT,value1 BIGINT,value2 BIGINT);

--插入数据
INSERT OVERWRITE TABLE acid_update_t VALUES(2,20,21),(3,30,31),(4,40,41),(5,50,51);
INSERT OVERWRITE TABLE acid_update_s VALUES (1,100,101),(2,200,201),(3,300,301),(4,400,401),(5,500,501);
INSERT OVERWRITE TABLE acid_update_m VALUES(3,30,101),(4,400,201),(5,300,301);

--查询acid_update_t表数据
SELECT * FROM acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
| 5          | 50         | 51         |
+------------+------------+------------+

--查询acid_update_s表数据
SELECT * FROM acid_update_s;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | 400        | 401        |
| 5          | 500        | 501        |
+------------+------------+------------+

--查询acid_update_m表数据
SELECT * FROM acid_update_m;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 3          | 30         | 101        |
| 4          | 400        | 201        |
| 5          | 300        | 301        |
+------------+------------+------------+

--关联更新，并且在where中同时对原表和目标表进行过滤
UPDATE acid_update_t 
SET value1 = acid_update_s.value1, value2 = acid_update_s.value2 
FROM acid_update_s 
WHERE acid_update_t.id = acid_update_s.id 
AND acid_update_s.id > 2 
AND acid_update_t.value1 NOT IN 
(SELECT value1 FROM acid_update_m WHERE id = acid_update_t.id) 
AND acid_update_s.value1 NOT IN 
(SELECT value1 FROM acid_update_m WHERE id = acid_update_s.id);

--查看更新结果，acid_update_t表只有id为5的数据符合条件，对应value1被更新为500，valu2被更新为501
SELECT * FROM acid_update_t;
--返回结果
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 5          | 500        | 501        |
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

  • 示例6：创建Delta Table表mf_dt，并导入数据，执行UPDATE操作删除满足指定条件的行。命令示例如下：

    --创建目标Delta Table表mf_dt。
CREATE TABLE IF  NOT EXISTS mf_dt (pk BIGINT  NOT NULL PRIMARY KEY, 
                  val  BIGINT NOT NULL) 
                  PARTITIONED BY(dd STRING, hh STRING) 
                  tblproperties ("transactional"="true");

--插入数据
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                 VALUES (1, 1), (2, 2), (3, 3);

--查看插入结果                 
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
--返回结果
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

--更新指定行的一列数据，将分区为01和02的所有pk=3的行，val值更新为30。
--方法一
UPDATE mf_dt SET val = 30 WHERE pk = 3 AND dd='01' AND hh='02';

--方法二
UPDATE mf_dt SET val = delta.val 
              FROM (SELECT pk, val FROM VALUES (3, 30) t (pk, val)) delta 
              WHERE delta.pk = mf_dt.pk AND mf_dt.dd='01' AND mf_dt.hh='02';

--查看更新结果。
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
--返回结果，pk=3的行val值被更新为30。
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 30         | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

相关命令

  • DELETE：用于删除Transactional分区表或非分区表中满足指定条件的单行或多行数据。

  • ALTER TABLE：合并Transactional表文件。

上一篇：UNLOAD下一篇：UPDATE FUNCTION