> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Choose which rows MERGE updates

> Update selected fields and apply conditions without changing the meaning of a

TutorialApplies to: Unified engine deployments

Update selected fields and apply conditions without changing the meaning of a
match.

Keep the business key in ON. Put action rules such as a newer source version in
WHEN MATCHED AND. A row that fails ON is an unmatched source row and may qualify
for INSERT. Updating one column leaves other target columns unchanged.
Expressions can read both source and target values.

## Before you start

You need a writable Iceberg catalog with namespace creation, table creation, and
MERGE support. These examples create format-v2 tables in a private namespace.
They use inline data and do not need an external sample dataset.

Choose an unused namespace. The examples use `learn_merge_02`. Replace it
everywhere if it already exists. In Workbench, select your writable
catalog before running any SQL. Keep that catalog selected throughout the
lesson. Opening an example does not execute it.

## Set up the namespace

Run this setup once:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SET spark.sql.caseSensitive = true;
CREATE NAMESPACE learn_merge_02;
```

Each example creates a separate table. Run an example's setup once. To start it
again, remove only that example's table first, using the cleanup section.
Scripts execute as separate operations; they are not a multi-statement
transaction.

## 1. Change balance while preserving the customer name

Start with these target rows:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 200 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE learn_merge_02.selected_columns (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_02.selected_columns VALUES (1, 'Ada', 100, 1, false), (2, 'Ben', 200, 2, false), (3, 'Cyd', 300, 1, false);
```

Apply the incoming source below:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
MERGE INTO learn_merge_02.selected_columns AS t
USING (SELECT 2 AS id, 'Incoming' AS name, 250 AS balance, 3 AS version, false AS is_deleted) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET balance = s.balance;
```

Check all rows:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT id, name, balance, version, is_deleted FROM learn_merge_02.selected_columns;
```

Expect these exact rows, in any display order:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 250 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

## 2. Ignore an older customer version

Start with these target rows:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 200 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE learn_merge_02.conditional_update (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_02.conditional_update VALUES (1, 'Ada', 100, 1, false), (2, 'Ben', 200, 2, false), (3, 'Cyd', 300, 1, false);
```

Apply the incoming source below:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
MERGE INTO learn_merge_02.conditional_update AS t
USING (SELECT 1 AS id, 'Old Ada' AS name, 50 AS balance, 0 AS version, false AS is_deleted UNION ALL SELECT 2, 'Ben', 250, 3, false) AS s
ON t.id = s.id
WHEN MATCHED AND s.version > t.version THEN UPDATE SET name = s.name, balance = s.balance, version = s.version, is_deleted = s.is_deleted
WHEN NOT MATCHED THEN INSERT (id,name,balance,version,is_deleted) VALUES (s.id,s.name,s.balance,s.version,s.is_deleted);
```

Check all rows:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT id, name, balance, version, is_deleted FROM learn_merge_02.conditional_update;
```

Expect these exact rows, in any display order:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 250 | 3 | false |
| 3 | Cyd | 300 | 1 | false |

## 3. Keep the version condition in the matched action

Start with these target rows:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 200 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE learn_merge_02.on_versus_when (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_02.on_versus_when VALUES (1, 'Ada', 100, 1, false), (2, 'Ben', 200, 2, false), (3, 'Cyd', 300, 1, false);
```

Apply the incoming source below:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
MERGE INTO learn_merge_02.on_versus_when AS t
USING (SELECT 2 AS id, 'Older Ben' AS name, 50 AS balance, 1 AS version, false AS is_deleted) AS s
ON t.id = s.id
WHEN MATCHED AND s.version > t.version THEN UPDATE SET name = s.name, balance = s.balance, version = s.version, is_deleted = s.is_deleted
WHEN NOT MATCHED THEN INSERT (id,name,balance,version,is_deleted) VALUES (s.id,s.name,s.balance,s.version,s.is_deleted);
```

Check all rows:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT id, name, balance, version, is_deleted FROM learn_merge_02.on_versus_when;
```

Expect these exact rows, in any display order:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 200 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

## 4. See why an ON filter can turn an old row into an insert

Start with these target rows:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 200 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE learn_merge_02.on_condition (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_02.on_condition VALUES (1, 'Ada', 100, 1, false), (2, 'Ben', 200, 2, false), (3, 'Cyd', 300, 1, false);
```

Apply the incoming source below:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
MERGE INTO learn_merge_02.on_condition AS t
USING (SELECT 2 AS id, 'Older Ben' AS name, 50 AS balance, 1 AS version, false AS is_deleted) AS s
ON t.id = s.id AND s.version > t.version
WHEN MATCHED THEN UPDATE SET name = s.name, balance = s.balance, version = s.version
WHEN NOT MATCHED THEN INSERT (id,name,balance,version,is_deleted) VALUES (s.id,s.name,s.balance,s.version,s.is_deleted);
```

Check all rows:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT id, name, balance, version, is_deleted FROM learn_merge_02.on_condition;
```

Expect these exact rows, in any display order:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 200 | 2 | false |
| 3 | Cyd | 300 | 1 | false |
| 2 | Older Ben | 50 | 1 | false |

Customer 2 now appears twice. The older version did not match the ON condition,
so the INSERT clause added it. This is the reason to keep the version guard in
the action.

## 5. Calculate a changed value from target and source

Start with these target rows:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 200 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
CREATE TABLE learn_merge_02.expression (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_02.expression VALUES (1, 'Ada', 100, 1, false), (2, 'Ben', 200, 2, false), (3, 'Cyd', 300, 1, false);
```

Apply the incoming source below:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
MERGE INTO learn_merge_02.expression AS t
USING (SELECT 2 AS id, 'Ben' AS name, 25 AS balance, 3 AS version, false AS is_deleted) AS s
ON t.id = s.id
WHEN MATCHED THEN UPDATE SET balance = t.balance + s.balance;
```

Check all rows:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT id, name, balance, version, is_deleted FROM learn_merge_02.expression;
```

Expect these exact rows, in any display order:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 2 | Ben | 225 | 2 | false |
| 3 | Cyd | 300 | 1 | false |

## Change one thing

Start the relevant example again from its original target rows. Drop only that
example table, rerun its CREATE TABLE and INSERT statements, then apply the
changed MERGE. Keep the lesson namespace.

In the selected-columns example, also assign name = s.name. Customer 2 should
now be named Incoming. All other rows should stay unchanged.

## Check the result

Compare every row and field with the expected tables, including unchanged
customers and the number of copies of each key. A Succeeded status alone is not
the result check. Wait for confirmed publication before reading the target.

## Clean up

Remove only the private tables created by this lesson:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
DROP TABLE IF EXISTS learn_merge_02.selected_columns;
DROP TABLE IF EXISTS learn_merge_02.conditional_update;
DROP TABLE IF EXISTS learn_merge_02.on_versus_when;
DROP TABLE IF EXISTS learn_merge_02.on_condition;
DROP TABLE IF EXISTS learn_merge_02.expression;
DROP NAMESPACE learn_merge_02;
```

## Continue

[Combine MERGE actions](https://docs.e6data.com/transformation/learn/merge-actions.md) or review the
[MERGE SQL reference](https://docs.e6data.com/transformation/sql/writes.md#merge).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.