# `MERGE INTO` — guide
Change rows in a skade table with one SQL statement.
## The statement
```rust
use skade_sql::WarehouseSql;
let wh = skade::open("/data/lake").await?;
let ctx = wh.session().await?;
// the source can be anything the session can see: another skade table, a
// registered Arrow batch, a CSV, a subquery
ctx.register_batch("incoming", batch)?;
ctx.sql("
MERGE INTO events USING incoming ON events.id = incoming.id
WHEN MATCHED THEN UPDATE SET status = incoming.status, seen_at = incoming.seen_at
WHEN NOT MATCHED THEN INSERT (id, status, seen_at)
VALUES (incoming.id, incoming.status, incoming.seen_at)
").await?.collect().await?;
```
The statement returns one row, one column:
```text
+-------+
| count |
+-------+
| 1417 |
+-------+
```
`count` is `rows updated + rows deleted + rows inserted` — what the *table*
changed by, not how many rows the source had. A merge with 5 000 source rows of
which 3 match returns 3.
Bare names (`events`) and catalog paths (`skade.main.events`) are the same
writable object. `Table::sql` on a `skade::Table` handle is still a
snapshot-pinned read-only view; use the warehouse session for anything that
writes.
## Every clause shape that executes
```sql
WHEN MATCHED THEN UPDATE SET col = expr [, col = expr ...]
WHEN MATCHED THEN DELETE
WHEN MATCHED AND <predicate> THEN UPDATE SET ...
WHEN MATCHED AND <predicate> THEN DELETE
WHEN NOT MATCHED [BY TARGET] THEN INSERT (cols) VALUES (exprs)
WHEN NOT MATCHED [BY TARGET] THEN INSERT VALUES (exprs) -- all columns
WHEN NOT MATCHED BY SOURCE THEN UPDATE SET ...
WHEN NOT MATCHED BY SOURCE THEN DELETE
```
Clauses are tried in written order; the first one of the applicable category
whose predicate holds wins. A predicate that evaluates to NULL does not fire —
same rule as `WHERE`.
`WHEN NOT MATCHED` and `WHEN NOT MATCHED BY TARGET` mean the same thing.
## Every shape that is refused, and what it says
| you wrote | what comes back |
|---|---|
| `ON t.a > s.a` | *"needs an ON condition that is a conjunction of `target.col = source.col` equalities"* |
| `ON t.id = s.id` with `id` `Int64` vs `Int32` | *"the join key types must match exactly. Cast in the USING subquery."* |
| `MERGE INTO` a partitioned table | *"a rewritten row needs its partition values recomputed, which this path does not do"* |
| `WHEN NOT MATCHED BY SOURCE THEN INSERT` | *"it names no row to act on. Refusing rather than silently doing nothing."* |
| `WHEN NOT MATCHED THEN DELETE` | sqlparser refuses it before DataFusion: *"DELETE is not allowed in a NOT MATCHED merge clause"* |
| a source with two rows for one target key | *"matched the same target row … with more than one source row … Narrow the ON condition or de-duplicate the source."* |
None of these silently does nothing. That is the point of every one of them.
## What it does to the table
Copy-on-write. Each data file that holds a changed row is **rewritten** with the
new content; files that hold none are carried into the new snapshot untouched.
One `overwrite` snapshot commits the swap.
Consequences worth knowing before you run it on a big table:
- **A merge that matches nothing commits nothing.** No snapshot, no new files,
the head does not move, `count` is 0.
- **Cost tracks the size of the files you hit, not the number of rows you
change.** Updating 100 rows spread over ten 20 MB files rewrites ~200 MB.
Updating 100 rows inside one file rewrites one file. Cluster your writes if
you can.
- **History is intact.** The pre-merge snapshot still reads back through
`RedbCatalog::load_table_at`; the old data files stay on disk until the
snapshot is expired. That is also why disk grows.
- **Concurrency is optimistic.** Two merges racing on one table: the second
fails its snapshot-ref requirement (or its not-live-file check) and nothing is
half-applied. Retry it.
## When NOT to use it
Use `skade::Table::delete_equality` + `Table::append` — merge-on-read — instead
when the write rate is high and the reader is a delta consumer rather than a
full scan. It writes ~48× less and commits ~17× faster, and it makes every
subsequent full scan slower, linearly in the number of delete files it has
accumulated (measured 38 ms → 173 ms over 8 rounds; see
`merge-into-design.md`). If you take that path, compact.
## Running the numbers yourself
```sh
cargo test --release --test merge_cow_vs_mor -- --ignored --nocapture
```
Prints the copy-on-write and merge-on-read columns side by side for keys spread
across every data file and for keys concentrated in one.