Gunakan UPDATE untuk memperbarui data kolom dalam tabel - MaxCompute

Pernyataan UPDATE memperbarui nilai satu atau beberapa kolom dalam tabel Transactional atau Delta Table.

Prasyarat

Anda harus memiliki izin Select dan Update pada tabel transaksional tempat Anda ingin menjalankan pernyataan DELETE atau UPDATE. Untuk informasi selengkapnya, lihat izin MaxCompute.

Batasan

Fitur DELETE dan UPDATE hanya dapat digunakan pada tabel Transactional dan Delta Table, serta tunduk pada batasan berikut:
Catatan
Untuk informasi selengkapnya mengenai tabel Transactional dan Delta Table, lihat Parameter untuk Tabel Transactional dan Delta Table.
- Sintaksis UPDATE untuk Delta Table tidak mendukung modifikasi kolom primary key (PK).

Peringatan

Pertimbangkan hal-hal berikut saat menggunakan operasi DELETE atau UPDATE untuk menghapus atau memperbarui data dalam tabel atau partisi:

Untuk menghapus atau memperbarui jumlah data yang kecil dalam sebuah tabel, dan baik operasi maupun pembacaan berikutnya jarang dilakukan, gunakan operasi DELETE dan UPDATE. Setelah melakukan beberapa operasi penghapusan atau pembaruan, gabungkan file dasar dan file Delta tabel tersebut untuk mengurangi jejak penyimpanannya. Untuk informasi selengkapnya, lihat Gabungkan file tabel Transactional.
Jika Anda menghapus atau memperbarui banyak baris (lebih dari 5%) secara jarang, tetapi operasi pembacaan berikutnya pada tabel tersebut sering dilakukan, gunakan INSERT OVERWRITE atau INSERT INTO. Untuk informasi selengkapnya, lihat Masukkan atau timpa data (INSERT INTO | INSERT OVERWRITE).
Misalnya, dalam skenario bisnis tertentu, Anda menghapus atau memperbarui 10% data sebanyak 10 kali sehari. Evaluasi apakah biaya dan degradasi performa baca akibat operasi DELETE dan UPDATE lebih rendah dibandingkan dengan menggunakan INSERT OVERWRITE atau INSERT INTO untuk setiap operasi. Bandingkan efisiensi kedua metode tersebut dalam skenario spesifik Anda untuk memilih opsi yang paling sesuai.
Menghapus data menghasilkan file Delta, yang berarti operasi tersebut tidak langsung mengurangi ruang penyimpanan. Jika Anda ingin mengurangi penyimpanan menggunakan operasi DELETE, Anda harus menggabungkan file dasar dan file Delta tabel tersebut. Untuk informasi selengkapnya, lihat Gabungkan file tabel Transactional.
MaxCompute menjalankan pekerjaan DELETE dan UPDATE sebagai proses batch. Setiap pernyataan mengonsumsi sumber daya dan dikenai biaya. Anda sebaiknya menghapus atau memperbarui data secara batch. Misalnya, jika Anda menggunakan skrip Python untuk menghasilkan dan mengirimkan banyak pekerjaan pembaruan tingkat baris di mana setiap pernyataan hanya beroperasi pada satu atau beberapa baris saja, setiap pernyataan akan dikenai biaya berdasarkan jumlah data masukan yang dipindai oleh SQL. Biaya kumulatif dari banyak pernyataan semacam ini secara signifikan meningkatkan pengeluaran dan menurunkan efisiensi sistem. Berikut adalah contoh perintahnya.
- Metode yang direkomendasikan:
```
UPDATE table1 SET col1= (SELECT value1 FROM table2 WHERE table1.id = table2.id AND table1.region = table2.region);
```
- Metode yang tidak direkomendasikan:
```
UPDATE table1 SET col1=1 WHERE id='2021063001' AND region='beijing';                 
UPDATE table1 SET col1=2 WHERE id='2021063002' AND region='beijing';
```

Sintaksis

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

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

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

Parameter

table_name: Wajib. Nama tabel Transactional atau Delta Table untuk operasi UPDATE.
alias: Opsional. Alias untuk tabel tersebut.
col1_name, col2_name: Wajib. Nama kolom yang akan dimodifikasi. Anda harus memperbarui setidaknya satu kolom.
value1, value2: Wajib. Nilai baru untuk kolom-kolom tersebut. Anda harus memperbarui setidaknya satu nilai kolom.
where_condition: Opsional. Klausul WHERE untuk memfilter data. Untuk informasi selengkapnya mengenai klausul WHERE, lihat sintaksis SELECT. Jika Anda tidak menyertakan klausul WHERE, semua data dalam tabel akan diperbarui.

additional_tables: Opsional. Klausul FROM.

Pernyataan UPDATE mendukung klausul FROM, yang dapat menyederhanakan pernyataan UPDATE. Tabel berikut membandingkan pernyataan UPDATE yang menggunakan klausul FROM dengan yang tidak menggunakannya.

Skenario

Kode contoh

Tanpa klausul 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);

Dengan klausul 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;

Seperti yang ditunjukkan oleh contoh kode tersebut:

Saat Anda memperbarui satu baris dalam tabel target menggunakan beberapa baris dari tabel sumber, Anda harus menggunakan operasi agregat untuk memastikan bahwa data sumber bersifat unik karena sistem tidak tahu baris sumber mana yang harus digunakan. Sintaksis tanpa klausa `FROM` kurang ringkas. Sintaksis dengan klausa `FROM` lebih sederhana dan mudah dipahami.
Saat melakukan pembaruan join, jika Anda hanya ingin memperbarui irisan data, sintaksis tanpa klausa `FROM` memerlukan kondisi `WHERE` tambahan dan kurang ringkas dibandingkan sintaksis dengan klausa `FROM`.

Contoh

Contoh 1: Buat tabel non-partisi bernama acid_update, impor data, lalu jalankan operasi UPDATE untuk memperbarui kolom pada baris yang memenuhi kondisi tertentu. Berikut adalah perintah contohnya:

-- Buat tabel Transactional bernama acid_update.  
CREATE TABLE IF  NOT EXISTS acid_update(id BIGINT) tblproperties ("transactional"="true");

-- Masukkan data.
INSERT OVERWRITE TABLE acid_update VALUES(1),(2),(3),(2);

-- Lihat data yang dimasukkan.
SELECT * FROM acid_update; 

-- Hasil berikut dikembalikan:
+------------+
| id         |
+------------+
| 1          |
| 2          |
| 3          |
| 2          |
+------------+

-- Perbarui nilai id menjadi 4 untuk semua baris dengan id = 2.
UPDATE acid_update SET id = 4 WHERE id = 2; 

-- Lihat hasil pembaruan. Nilai 2 telah diperbarui menjadi 4.
SELECT * FROM acid_update; 

-- Hasil berikut dikembalikan:
+------------+
| id         |
+------------+
| 1          |
| 3          |
| 4          |
| 4          |
+------------+

Contoh 2: Buat tabel partisi bernama acid_update, impor data, lalu jalankan operasi UPDATE untuk memperbarui kolom pada baris yang memenuhi kondisi tertentu. Berikut adalah perintah contohnya:

-- Buat tabel Transactional bernama acid_update_pt. 
CREATE TABLE IF  NOT EXISTS acid_update_pt(id BIGINT) PARTITIONED BY(ds STRING) tblproperties ("transactional"="true");

-- Tambahkan partisi.
ALTER TABLE acid_update_pt ADD IF  NOT EXISTS PARTITION (ds= '2019');

-- Masukkan data.
INSERT OVERWRITE TABLE acid_update_pt PARTITION (ds='2019') VALUES(1),(2),(3);

-- Lihat data yang dimasukkan.
SELECT * FROM acid_update_pt WHERE ds = '2019'; 

-- Hasil berikut dikembalikan:
+------------+------------+
| id         | ds         |
+------------+------------+
| 1          | 2019       |
| 2          | 2019       |
| 3          | 2019       |
+------------+------------+

-- Perbarui kolom pada baris tertentu. Atur nilai id menjadi 4 untuk semua baris dengan partisi 2019 dan id = 2.
UPDATE acid_update_pt SET id = 4 WHERE ds = '2019' AND id = 2; 

-- Lihat hasil pembaruan. Nilai 2 telah diperbarui menjadi 4.
SELECT * FROM acid_update_pt WHERE ds = '2019';

-- Hasil berikut dikembalikan:
+------------+------------+
| id         | ds         |
+------------+------------+
| 4          | 2019       |
| 1          | 2019       |
| 3          | 2019       |
+------------+------------+

Contoh 3: Buat tabel target bernama acid_update_t dan tabel terkait bernama acid_update_s untuk memperbarui beberapa nilai kolom sekaligus. Berikut adalah perintah contohnya:

-- Buat tabel Transactional target yang akan diperbarui, bernama acid_update_t, dan tabel terkait bernama 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); 

-- Masukkan data.
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);

-- Metode 1: Perbarui dengan konstanta.
UPDATE acid_update_t SET (value1, value2) = (60,61);
-- Kueri data hasil pada tabel target untuk Metode 1.
SELECT * FROM acid_update_t;

-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 60         | 61         |
| 3          | 60         | 61         |
| 4          | 60         | 61         |
+------------+------------+------------+

-- Metode 2: Pembaruan join. Aturannya adalah left join dari acid_update_t ke 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);
-- Kueri data hasil pada tabel target untuk Metode 2.
SELECT * FROM acid_update_t;

-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

-- Metode 3 (pembaruan berdasarkan hasil Metode 2): Pembaruan join. Aturannya adalah menambahkan kondisi filter untuk hanya memperbarui irisan.
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);
-- Kueri data hasil pada tabel target untuk Metode 3.
SELECT * FROM acid_update_t;

-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

-- Metode 4 (pembaruan berdasarkan hasil Metode 3): Pembaruan join dengan hasil agregat.
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);
-- Kueri data hasil pada tabel target untuk Metode 4.
SELECT * FROM acid_update_t;

-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | NULL       | NULL       |
+------------+------------+------------+

Contoh 4: Kueri join sederhana yang melibatkan dua tabel. Berikut adalah perintah contohnya:

-- Buat tabel target untuk pembaruan, acid_update_t, dan tabel terkait, 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);

-- Masukkan data.
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);

-- Kueri data dari tabel acid_update_t.
SELECT * FROM acid_update_t;
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

-- Kueri data dari tabel acid_update_s.
SELECT * FROM acid_update_s;
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

-- Pembaruan join. Tambahkan kondisi filter pada tabel target untuk hanya memperbarui irisan.
UPDATE acid_update_t SET value1 = b.value1, value2 = b.value2
FROM acid_update_s b WHERE acid_update_t.id = b.id;
-- Perintah berikut setara dengan perintah di atas.
UPDATE acid_update_t a SET a.value1 = b.value1, a.value2 = b.value2 
FROM acid_update_s b WHERE a.id = b.id;

-- Lihat hasil pembaruan. 20 diperbarui menjadi 200, 21 menjadi 201, 30 menjadi 300, dan 31 menjadi 301.
SELECT * FROM acid_update_t;
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 4          | 40         | 41         |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
+------------+------------+------------+

Contoh 5: Kueri join kompleks yang melibatkan beberapa tabel. Berikut adalah perintah contohnya:

-- Buat tabel target untuk pembaruan, acid_update_t, dan tabel terkait, 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);

-- Masukkan data.
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);

-- Kueri data dari tabel acid_update_t.
SELECT * FROM acid_update_t;
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
| 5          | 50         | 51         |
+------------+------------+------------+

-- Kueri data dari tabel acid_update_s.
SELECT * FROM acid_update_s;
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 1          | 100        | 101        |
| 2          | 200        | 201        |
| 3          | 300        | 301        |
| 4          | 400        | 401        |
| 5          | 500        | 501        |
+------------+------------+------------+

-- Kueri data dari tabel acid_update_m.
SELECT * FROM acid_update_m;
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 3          | 30         | 101        |
| 4          | 400        | 201        |
| 5          | 300        | 301        |
+------------+------------+------------+

-- Pembaruan join, dan filter kedua tabel sumber dan target dalam klausa 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);

-- Lihat hasil pembaruan. Hanya data dalam tabel acid_update_t dengan id = 5 yang memenuhi kondisi. Nilai value1 yang sesuai diperbarui menjadi 500, dan value2 diperbarui menjadi 501.
SELECT * FROM acid_update_t;
-- Hasil berikut dikembalikan:
+------------+------------+------------+
| id         | value1     | value2     |
+------------+------------+------------+
| 5          | 500        | 501        |
| 2          | 20         | 21         |
| 3          | 30         | 31         |
| 4          | 40         | 41         |
+------------+------------+------------+

Contoh 6: Perintah berikut merupakan contoh cara membuat Delta table bernama mf_dt, mengimpor data, dan menjalankan operasi UPDATE untuk memperbarui baris yang memenuhi kondisi tertentu:

-- Buat Delta Table target bernama 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");

-- Masukkan data.
INSERT OVERWRITE TABLE mf_dt PARTITION (dd='01', hh='02') 
                 VALUES (1, 1), (2, 2), (3, 3);

-- Lihat data yang dimasukkan.                 
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
-- Hasil berikut dikembalikan:
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 3          | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

-- Perbarui kolom pada baris tertentu. Atur nilai val menjadi 30 untuk semua baris dengan partisi 01 dan 02, serta pk = 3.
-- Metode 1
UPDATE mf_dt SET val = 30 WHERE pk = 3 AND dd='01' AND hh='02';

-- Metode 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';

-- Lihat hasil pembaruan.
SELECT * FROM mf_dt WHERE dd='01' AND hh='02';
-- Hasil berikut dikembalikan. Nilai val untuk baris dengan pk=3 diperbarui menjadi 30.
+------------+------------+----+----+
| pk         | val        | dd | hh |
+------------+------------+----+----+
| 1          | 1          | 01 | 02 |
| 3          | 30         | 01 | 02 |
| 2          | 2          | 01 | 02 |
+------------+------------+----+----+

Pernyataan terkait

DELETE: Menghapus satu atau beberapa baris yang memenuhi kondisi tertentu dari tabel Transactional atau Delta Table.
ALTER TABLE: Menggabungkan file-file tabel Transactional.