# Replace a slice of the table

Join / merge upsert

A variant of [Merge into a table](https://meridian.online/operations/merge-into-a-table).

Delete the target rows in the partitions, time range or keys the new data covers, then insert the new rows.

Page: <https://meridian.online/operations/merge-into-a-table/replace-a-slice-of-the-table>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

replace the slice of the target that the new data covers

```sql
CREATE TEMP TABLE sales AS SELECT * FROM (VALUES
  (1, 'north', DATE '2026-01-03', 10), (2, 'north', DATE '2026-01-20', 20), (3, 'south', DATE '2026-01-05', 30),
  (4, NULL, DATE '2026-01-06', 40), (5, 'south', DATE '2026-02-02', 50), (6, 'east', NULL, 60), (9, 'south', DATE '2026-01-10', 90)) t(order_id, region, order_date, amount);
CREATE TEMP TABLE fresh AS SELECT * FROM (VALUES
  (2, 'north', DATE '2026-01-20', 21), (7, 'north', DATE '2026-01-25', 70), (8, NULL, DATE '2026-01-07', 80)) t(order_id, region, order_date, amount);
CREATE TEMP TABLE by_partition AS SELECT * FROM sales;
CREATE TEMP TABLE by_range AS SELECT * FROM sales;
CREATE TEMP TABLE by_key AS SELECT * FROM sales;
-- partitions: the regions fresh covers, NULL included (IN would leave the NULL region's old rows in place)
BEGIN;
DELETE FROM by_partition WHERE EXISTS (SELECT 1 FROM fresh WHERE fresh.region IS NOT DISTINCT FROM by_partition.region);
INSERT INTO by_partition SELECT * FROM fresh;
COMMIT;
-- time range: from the earliest to the latest date fresh holds; a row with no date is outside every range
BEGIN;
DELETE FROM by_range WHERE order_date BETWEEN (SELECT min(order_date) FROM fresh) AND (SELECT max(order_date) FROM fresh);
INSERT INTO by_range SELECT * FROM fresh;
COMMIT;
-- keys: the order ids fresh holds
BEGIN;
DELETE FROM by_key WHERE EXISTS (SELECT 1 FROM fresh WHERE fresh.order_id = by_key.order_id);
INSERT INTO by_key SELECT * FROM fresh;
COMMIT;
SELECT * FROM by_partition ORDER BY order_id;
SELECT CASE WHEN ((SELECT list(order_id ORDER BY order_id) = [2, 3, 5, 6, 7, 8, 9] AND max(amount) FILTER (WHERE order_id = 2) = 21 FROM by_partition)
  AND (SELECT list(order_id ORDER BY order_id) = [1, 2, 3, 4, 5, 6, 7, 8] AND max(amount) FILTER (WHERE order_id = 2) = 21 FROM by_range)
  AND (SELECT list(order_id ORDER BY order_id) = [1, 2, 3, 4, 5, 6, 7, 8, 9] AND max(amount) FILTER (WHERE order_id = 2) = 21 FROM by_key)) THEN 'as expected' ELSE error('replacing by partition should drop the old north and NULL-region rows (1, 2 and 4), by time range the rows dated 2026-01-07 to 2026-01-25 (2 and 9), and by key only order 2, each followed by the 3 new rows') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Bruin: [`delete+insert`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27)
- Bruin: [`time_interval`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27)
- dbt-utils: [`delete+insert`](https://docs.getdbt.com/docs/build/incremental-strategy) (read 2026-09-27)
- dbt-utils: [`insert_overwrite`](https://docs.getdbt.com/docs/build/incremental-strategy) (read 2026-09-27)
- dbt-utils: [`microbatch`](https://docs.getdbt.com/docs/build/incremental-strategy) (read 2026-09-27)
- SQLMesh: [`INCREMENTAL_BY_PARTITION`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27)
- SQLMesh: [`INCREMENTAL_BY_TIME_RANGE`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27)