全部產品
Search

搜尋本產品

    MaxCompute：UPDATE

    文件中心

    MaxCompute：UPDATE

    更新時間：Jun 19, 2024

    update操作用於將Transactional分區表或非分區表中行對應的單列或多列資料更新為新值。

    前提條件

    執行delete、update操作前需要具備目標Transactional表的讀取表資料許可權（Select）及更新表資料許可權（Update）。授權操作請參見MaxCompute許可權。

    使用限制

    delete、update功能及對應Transactional表的使用限制如下：
    • 僅支援Transactional表。更多建立Transactional表資訊，請參見CREATE TABLE。
    • 在對Transactional表的重要資料執行update、delete或insert overwrite操作前需要手動通過select+insert操作將資料備份至其他表中。

    注意事項

    通過delete、update操作刪除或更新表或分區內的資料時，注意事項如下：
    • 如果需要對錶中較少資料進行刪除或更新操作，且操作和後續讀資料的頻率也不頻繁，建議使用delete、update操作，並且在多次執行刪除操作之後，請合并表的Base檔案和Delta檔案，降低表的實際儲存。更多資訊，請參見合并Transactional表檔案。
    • 如果刪除或更新行數較多（超過5%）並且操作不頻繁，但後續對該表的讀操作比較頻繁，建議使用insert overwrite或insert into操作。更多資訊，請參見INSERT INTO|OVERWRITE。

      例如，某業務情境為每次刪除或更新10%的資料，一天更新10次。建議根據實際情況評估delete、update操作產生的費用及後續對讀效能的消耗是否小於每次使用insert overwrite或insert into操作產生的費用及後續對讀效能的消耗，比較兩種方式在具體情境中的效率，選擇更優方案。

    • 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> set <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];

--方式2
update <table_name> set (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...])[WHERE <where_condition>];

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

    參數說明

    • table_name：必填。待執行update操作的Transactional表名稱。

    • col1_name、col2_name：至少更新一個。待修改行對應的列名稱。

    • value1、value2：至少更新一個列值。修改後的新值。

    • where_condition：可選。WHERE子句，用於篩選滿足條件的資料。更多WHERE子句資訊，請參見WHERE子句（where_condition）。如果不帶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) 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;

--查看更新結果，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         |
+------------+------------+------------+

    相關命令

    • DELETE：用於刪除Transactional分區表或非分區表中滿足指定條件的單行或多行資料。
    • ALTER TABLE：合并Transactional表檔案。