> ## 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.

# Combine MERGE actions

> Use update, insert, and delete clauses together and observe clause order.

TutorialApplies to: Unified engine deployments

Use update, insert, and delete clauses together and observe clause order.

Within a match group, MERGE uses the first qualifying clause. Put narrower rules
first. Only the last clause in a match group may omit its condition. A row stays
unchanged when none of its group's actions qualify.

## 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_03`. 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_03;
```

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. Delete flagged customers and insert active ones

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_03.mixed_actions (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_03.mixed_actions 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_03.mixed_actions AS t
USING (SELECT 2 AS id, 'Ben' AS name, 0 AS balance, 3 AS version, true AS is_deleted UNION ALL SELECT 4, 'Dan', 400, 1, false UNION ALL SELECT 5, 'Eve', 0, 1, true) AS s
ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED THEN UPDATE SET balance = s.balance
WHEN NOT MATCHED AND NOT s.is_deleted THEN INSERT *;
```

Check all rows:

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

Expect these exact rows, in any display order:

| id | name | balance | version | is\_deleted |
| - | - | - | - | - |
| 1 | Ada | 100 | 1 | false |
| 3 | Cyd | 300 | 1 | false |
| 4 | Dan | 400 | 1 | false |

## 2. Apply the first matching clause

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_03.clause_order (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_03.clause_order 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_03.clause_order AS t
USING (SELECT 2 AS id, 'Ben' AS name, 250 AS balance, 3 AS version, true AS is_deleted) AS s
ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN DELETE
WHEN MATCHED AND s.balance > 0 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_03.clause_order;
```

Expect these exact rows, in any display order:

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

## 3. Reverse the clause order and compare the rows

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_03.clause_order_reversed (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_03.clause_order_reversed 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_03.clause_order_reversed AS t
USING (SELECT 2 AS id, 'Ben' AS name, 250 AS balance, 3 AS version, true AS is_deleted) AS s
ON t.id = s.id
WHEN MATCHED AND s.balance > 0 THEN UPDATE SET balance = s.balance
WHEN MATCHED AND s.is_deleted THEN DELETE;
```

Check all rows:

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

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 |

## 4. Leave a matching row unchanged when no action qualifies

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_03.no_action (id BIGINT, name STRING, balance BIGINT, version BIGINT, is_deleted BOOLEAN)
USING iceberg TBLPROPERTIES ('format-version' = '2');
INSERT INTO learn_merge_03.no_action 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_03.no_action AS t
USING (SELECT 2 AS id, 'Ben' AS name, 250 AS balance, 3 AS version, false AS is_deleted) AS s
ON t.id = s.id
WHEN MATCHED AND s.is_deleted THEN DELETE;
```

Check all rows:

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

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 |

## 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.

Remove the NOT s.is\_deleted condition from the insert clause in the
mixed-actions example. Customer 5 should now be inserted with is\_deleted = true.

## 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_03.mixed_actions;
DROP TABLE IF EXISTS learn_merge_03.clause_order;
DROP TABLE IF EXISTS learn_merge_03.clause_order_reversed;
DROP TABLE IF EXISTS learn_merge_03.no_action;
DROP NAMESPACE learn_merge_03;
```

## Continue

[Synchronise a complete source snapshot](https://docs.e6data.com/transformation/learn/merge-snapshot.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.