UPDATE - MaxCompute - Alibaba Cloud ドキュメントセンター

パーティション化トランザクションテーブルまたは非パーティション化トランザクションテーブルで指定された条件を満たす行の1つ以上の列の値を更新します。

前提条件

DELETEまたはUpdateステートメントを実行するトランザクションテーブルに対して、[選択] および [更新] 権限が付与されています。詳細は、「MaxCompute権限」をご参照ください。

制限

DELETEステートメント、UPDATEステートメント、およびDELETEまたはUPDATEステートメントが実行されるトランザクションテーブルには、次の制限があります。

DELETEおよびUPDATEステートメントは、トランザクションテーブルでのみ実行できます。トランザクションテーブルの作成方法の詳細については、「create table」をご参照ください。
MaxComputeでは、テーブルを作成するときにのみ、テーブルをトランザクションテーブルに設定できます。 ALTER TABLEステートメントを実行して既存のテーブルをトランザクションテーブルに変更すると、エラーが返されます。
```
alter table not_txn_tbl set tblproperties("transactional"="true");
-- The following error is returned: 
FAILED: Catalog Service Failed, ErrorCode: 151, Error Message: Set transactional is not supported
```
クラスタ化テーブルまたは外部テーブルを作成する場合、トランザクションテーブルに設定することはできません。
トランザクションテーブルとMaxCompute内部テーブル、外部テーブル、またはクラスタ化テーブルを変換することはできません。
トランザクションテーブルファイルは自動的にマージできません。トランザクションテーブルファイルを手動でマージする必要があります。詳細については、UPDATE and DELETEのALTER TABLE COMPACTセクションをご参照ください。
MERGE PARTITION操作はサポートされていません。
他のシステムのジョブからトランザクションテーブルへのアクセスには特定の制限が課される。たとえば、ジョブがGraphジョブの場合、そのジョブを使用してトランザクションテーブルからデータを読み書きすることはできません。ジョブがSparkジョブまたはPlatform for AI (PAI) ジョブの場合、ジョブを使用してトランザクションテーブルからデータを読み取ることのみができ、ジョブを使用してトランザクションテーブルにデータを書き込むことはできません。
トランザクションテーブルの重要なデータに対してUPDATE、DELETE、またはINSERT OVERWRITEステートメントを実行する前に、SELECTおよびINSERTステートメントを実行して、データを他のテーブルにバックアップする必要があります。

注意事項

DELETEまたはUPDATEステートメントを実行して、テーブルまたはテーブルのパーティションのデータを削除または更新する場合は、次の項目に注意してください。

特定のシナリオでは、テーブル内の少量のデータに対してDELETEまたはUPDATEステートメントを実行し、後続の手順で読み取りやその他の操作を実行することはめったにありません。テーブルによって占有されるストレージ容量を減らすために、テーブルのDELETEまたはUPDATEステートメントを複数回実行した後に、ベースファイルをすべてのデルタファイルとマージすることをお勧めします。詳細については、「トランザクションテーブルのファイルのマージ」をご参照ください。
特定のシナリオでは、テーブルまたはテーブルのパーティション内の5% を超えるデータを低頻度で削除または更新し、後続の手順で頻繁に読み取り操作を実行することができます。このようなシナリオでは、INSERT OVERWRITEまたはINSERT INTOステートメントを実行することを推奨します。詳細については、「INSERT INTO | OVERWRITE」をご参照ください。
たとえば、10% のデータの削除または更新操作を1日に10回実行したいとします。この場合、テーブルに対してDELETEまたはUPDATEステートメントを実行すると、その後の読み取りパフォーマンスの総コストと消費量を見積もることを推奨します。次に、推定結果をINSERT OVERWRITEまたはINSERT INTOステートメントの実行結果と比較します。これは効率的な方法を選択するのに役立ちます。
MaxComputeは、ジョブで複数のDELETEおよびUPDATEステートメントを一度に実行します。各ステートメントはリソースを消費し、料金が発生します。一度にデータのバッチを削除または更新することを推奨します。たとえば、Pythonスクリプトを実行して多数の行レベルの更新ジョブを生成および送信し、各ステートメントが1行または少数の行のデータに対してのみ実行される場合、各ステートメントは、SQLステートメントによってスキャンされた入力データの量に対応する料金を負担し、関連するコンピューティングリソースを消費します。複数のステートメントが蓄積されると、コストが大幅に増加し、システム効率が低下します。例:
```
-- We recommend that you execute the following statement: 
update table1 set col1= (select value1 from table2 where table1.id = table2.id and table1.region = table2.region);

-- We recommend that you do not execute the following statements: 
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ステートメントを実行するトランザクションテーブルの名前。
col1_nameおよびcol2_name: 更新する列。少なくとも1つの列を指定する必要があります。このパラメーターには、指定したフィルター条件を満たす行を含む列の名前を指定します。
value1およびvalue2: 指定した列に割り当てる新しい値。少なくとも1つの列の値を更新する必要があります。このパラメーターは、指定されたフィルター条件を満たす行に対応する列の新しい値を指定します。
where_condition: オプション。条件に基づいてデータをフィルタリングするために使用されるWHERE句。詳細については、「WHERE句 (where_condition) 」をご参照ください。 WHERE句を持たないテーブルに対してUPDATEステートメントを実行すると、テーブル内のすべてのデータが更新されます。

additional_tables: オプション。 A from句。

UPDATEステートメントは、from句とともに使用できます。 from句は、UPDATEステートメントの使用をより便利にします。次の表では、from句の有無にかかわらず使用されるUPDATEステートメントについて説明します。

シナリオ

サンプルコード

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;

上記のサンプルコードに基づいて、次の結論が得られます。

ソーステーブルの複数行のデータを使用して、ターゲットテーブルの1行のデータを更新する場合は、データソースの一意性を確保するために集計操作を記述する必要があります。集計操作のコードは、from句を使用しない場合よりも、from句を使用する方が簡単でわかりやすくなります。
結合操作中に交差データのみを更新する必要がある場合は、from句またはwhere句を使用する必要があります。 where句は、from句よりも複雑です。

例

例1: acid_updateという名前のパーティション分割されていないテーブルを作成し、テーブルにデータを挿入します。次に、UPDATEステートメントを実行して、テーブル内の指定された条件を満たす行の列を更新します。サンプル文:

-- Create a non-partitioned table named acid_update.   
create table if not exists acid_update(id bigint) tblproperties ("transactional"="true");

-- Insert data into the table. 
insert overwrite table acid_update values(1),(2),(3),(2);

-- Query the table to check whether data is inserted. 
select * from acid_update; 

-- The following result is returned:
+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

-- Update the value 2 in the id column to 4. 
update acid_update set id = 4 where id = 2; 

-- Query the partition to check whether the value 2 is updated to 4 in the id column. 
select * from acid_update; 

-- The following result is returned:
+------------+
| id         |
+------------+
| 1          |
| 3          |
| 4          |
| 4          |
+------------+

例2: acid_updateという名前のパーティションテーブルを作成し、テーブルにデータを挿入します。次に、UPDATEステートメントを実行して、テーブル内の指定された条件を満たす行の列を更新します。サンプル文:

-- Create a partitioned table named acid_update_pt.  
create table if not exists acid_update_pt(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");

-- Add a partition to the table. 
alter table acid_update_pt add if not exists partition (ds= '2019');

-- Insert data into the partition. 
insert overwrite table acid_update_pt partition (ds='2019') values(1),(2),(3);

-- Query the partition to check whether data is inserted.
select * from acid_update_pt where ds = '2019';

-- The following result is returned:
+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+

-- Update the value 2 of the id column in the 2019 partition to 4. 
update acid_update_pt set id = 4 where ds = '2019' and id = 2; 

-- Query the partition to check whether the value 2 is updated to 4 in the id column. 
select * from acid_update_pt where ds = '2019';

-- The following result is returned:
+------------+------------+
| id         | ds         |
+------------+------------+
| 4          | 2019       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

例3: 更新するacid_update_tという名前のトランザクションテーブルと、acid_update_sという名前の関連テーブルを作成します。次に、acid_update_tテーブルの複数の列の値を一度に更新します。サンプル文:

-- Create a transactional table named acid_update_t that you want to update and an associated table named 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 data into the tables. 
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);

-- Method 1: Update the values of specific columns with constants. 
update acid_update_t set (value1, value2) = (60,61);
-- Query the acid_update_t table to check whether data is updated as expected. 
select * from acid_update_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 60         | 61         |
| 3          | 60         | 61         |
| 4          | 60         | 61         |
+------------+------------+------------+

-- Method 2: Use the data in the acid_update_s table to update all rows in the acid_update_t table. If specific rows in the acid_update_t table cannot be matched, null values are assigned to the rows. 
update acid_update_t set (value1, value2) = (select value1, value2 from acid_update_s where acid_update_t.id = acid_update_s.id);
-- Query the acid_update_t table to check whether data is updated as expected. 
select * from acid_update_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

-- Method 3: Use the data in the acid_update_s table to update the acid_update_t table. Add filter conditions for the acid_update_t table to update only the rows that intersect with those in the acid_update_s table. 
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);
-- Query the acid_update_t table to check whether data is updated as expected. 
select * from acid_update_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

-- Method 4: Use the aggregate results of the acid_update_t and acid_update_s tables to update the acid_update_t table. 
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);
-- Query the acid_update_t table to check whether data is updated as expected. 
select * from acid_update_t;

-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

例4: 2つのテーブルに対して結合操作を実行します。サンプル文:

-- Create a destination table named acid_update_t and create a table named acid_update_s for joins.
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 data into these tables.
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);

-- Query data from the acid_update_t table.
select * from acid_update_t;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

-- Query data from the acid_update_s table.
select * from acid_update_s;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

-- Use the data in the acid_update_s table to update the acid_update_t table. Add filter conditions for the acid_update_t table to update only the intersection of the two tables.
update acid_update_t set value1 = b.value1, value2 = b.value2
from acid_update_s b where acid_update_t.id = b.id;

-- Value 20 is updated to 200, value 21 is updated to 201, value 30 is updated to 300, and value 31 is updated to 301.
select * from acid_update_t;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 4          | 40         | 41         |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

例5: 3つ以上のテーブルに対して複雑な結合操作を実行します。サンプル文:

-- Create a destination table named acid_update_t and create a table named acid_update_s for joins.
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 data into these tables.
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);

-- Query data from the acid_update_t table.
select * from acid_update_t;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
| 5          | 50         | 51         |
+------------+------------+------------+

-- Query data from the acid_update_s table.
select * from acid_update_s;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | 400        | 401        |
| 5          | 500        | 501        |
+------------+------------+------------+

-- Query data from the acid_update_m table.
select * from acid_update_m;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 3          | 30         | 101        |
| 4          | 400        | 201        |
| 5          | 300        | 301        |
+------------+------------+------------+

-- Use the data in the acid_update_s table to update the acid_update_t table. Use the where clause to filter values in the acid_update_s, acid_update_t, and acid_update_m tables.
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);

-- View the update result. Only the row of data that contains the value of 5 in the id column of the acid_update_t table meets the condition. The value in the value1 column is updated to 500, and the value in the value2 column is updated to 501.
select * from acid_update_t;
-- The following result is returned:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 5          | 500        | 501        |
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

例6: mf_dtという名前のDeltaテーブルを作成し、テーブルにデータをインポートし、UPDATEステートメントを実行して、指定された条件を満たす行をテーブルから削除します。サンプル文:

-- Create a Delta table named 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 data into the table.
insert overwrite table mf_dt partition (dd='01', hh='02') 
                 values (1, 1), (2, 2), (3, 3);

-- Query the table to check whether data is inserted.                 
select * from mf_dt where dd='01' and hh='02';
-- The following result is returned:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

-- Update the value of the val column in the row whose value of the pk column is 3 in the dd='01' and hh='02' partition to 30. 
-- Method 1
update mf_dt set val = 30 where pk = 3 and dd='01' and hh='02';

-- Method 2
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';

-- View the update result. 
select * from mf_dt where dd='01' and hh='02';
-- The following result is returned. The value of the val column in the row whose value of the pk column is 3 is updated to 30. 
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 30         | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

MaxCompute:UPDATE

前提条件

制限

注意事項

構文

パラメーター

例

関連ステートメント