MaxCompute:Partial column update

How it works

Behavior for unspecified columns depends on the operation:

This distinction is the most important behavior to internalize: INSERT fills missing columns with NULL, while UPDATE and WHEN MATCHED leave them untouched.

Use cases

The most common use case is building wide tables from a star schema. When multiple source tables share the same primary key, write each source table's columns to the wide table independently and in parallel. At read time, MaxCompute merges rows by primary key and returns complete records. Compared with writing all columns in a single operation, this approach improves write and read performance and reduces storage costs.

Prerequisites

Before you begin, ensure that you have:

• A Delta table with partial column update enabled (see Create a Delta table)

• Familiarity with MaxCompute DML statements: INSERT INTO, UPDATE, DELETE, and MERGE INTO

Create a Delta table

To enable partial column update, set two table properties when creating the table:

CREATE TABLE delta_target
(
    key  BIGINT NOT NULL PRIMARY KEY,
    b    STRING,
    c    BIGINT
)
TBLPROPERTIES (
    "acid.partial.fields.update.enable" = "true",
    "transactional" = "true"
);

Both acid.partial.fields.update.enable and transactional must be set to "true". Partitioned and non-partitioned tables are both supported.

Insert data into specific columns

Use INSERT INTO to write values to a subset of columns. Columns not listed in the column list are set to NULL. When you insert a row with a primary key that already exists, MaxCompute updates only the specified columns and leaves the rest unchanged — this is effectively a partial update by insert.

Example 1: Insert a new row, omitting column `c`

Column c is not listed, so it is set to NULL.

INSERT INTO TABLE delta_target(key, b) VALUES(1, '1');

SELECT * FROM delta_target;

Result:

+------------+------------+------------+
| key        | b          | c          |
+------------+------------+------------+
| 1          | 1          | NULL       |
+------------+------------+------------+

Example 2: Insert a row with a duplicate primary key to update a specific column

A row with key=1 already exists. Inserting with the same primary key updates the specified column (c) and leaves the unspecified column (b) unchanged.

INSERT INTO TABLE delta_target(key, c) VALUES(1, 1);

SELECT * FROM delta_target;

Result:

+------------+------------+------------+
| key        | b          | c          |
+------------+------------+------------+
| 1          | 1          | 1          |
+------------+------------+------------+

Example 3: Update another column while keeping `c` unchanged

Column c is not listed, so it retains its current value of 1.

INSERT INTO TABLE delta_target(key, b) VALUES(1, '11');

SELECT * FROM delta_target;

Result:

+------------+------------+------------+
| key        | b          | c          |
+------------+------------+------------+
| 1          | 11         | 1          |
+------------+------------+------------+

Example 4: Insert a row with a new primary key

When the primary key does not already exist, a new row is inserted.

INSERT INTO TABLE delta_target VALUES(2, '2', 2);

SELECT * FROM delta_target;

Result:

+------------+------------+------------+
| key        | b          | c          |
+------------+------------+------------+
| 2          | 2          | 2          |
| 1          | 11         | 1          |
+------------+------------+------------+

For more information, see Insert data into or overwrite data in a table or a static partition (INSERT INTO and INSERT OVERWRITE).

Update specific columns in existing rows

Use UPDATE to modify specific columns in matching rows. Columns not listed in the SET clause are left unchanged.

Example 1: Update column `b` for `key=1`, leaving `c` unchanged

UPDATE delta_target SET b='111' WHERE key=1;

SELECT * FROM delta_target;

Result:

+------------+------------+------------+
| key        | b          | c          |
+------------+------------+------------+
| 2          | 2          | 2          |
| 1          | 111        | 1          |
+------------+------------+------------+

Example 2: Update column `b` for `key=2`, leaving `c` unchanged

UPDATE delta_target SET b='222' WHERE key=2;

SELECT * FROM delta_target;

Result:

+------------+------------+------------+
| key        | b          | c          |
+------------+------------+------------+
| 2          | 222        | 2          |
| 1          | 111        | 1          |
+------------+------------+------------+

For more information, see UPDATE and DELETE.

Merge source data into specific columns

Use MERGE INTO to upsert data while targeting only specific columns. In the WHEN MATCHED clause, only the listed columns are updated; others are left unchanged. In the WHEN NOT MATCHED clause, unlisted columns are set to NULL.

The following example creates a source table and merges it into delta_target. The merge updates column b for matching rows and inserts (key, b) for new rows — column c is not written in either case.

-- Create a source table with six rows
CREATE TABLE acid2_dml_pu_source AS
SELECT key, b, c
FROM VALUES
    (1, '10', 10),
    (2, '20', 20),
    (3, '30', 30),
    (4, '40', 40),
    (5, '50', 50),
    (6, '60', 60) t (key, b, c);

-- Merge: update b for matching rows, insert (key, b) for new rows
MERGE INTO delta_target AS t
USING acid2_dml_pu_source AS s ON s.key = t.key
WHEN MATCHED THEN
    UPDATE SET t.b = s.b
WHEN NOT MATCHED THEN
    INSERT (key, b) VALUES(s.key, s.b);

Result:

+------------+------------+------------+
| key        | b          | c          |
+------------+------------+------------+
| 3          | 30         | NULL       |
| 4          | 40         | NULL       |
| 5          | 50         | NULL       |
| 6          | 60         | NULL       |
| 2          | 20         | 2          |
| 1          | 10         | 1          |
+------------+------------+------------+

Rows 3–6 are new inserts: column c is NULL because it was not specified. Rows 1 and 2 were matched: column b was updated and c retained its previous value.

For more information, see MERGE INTO.

What's next

• INSERT INTO and INSERT OVERWRITE

• UPDATE and DELETE

• MERGE INTO