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

# Merge

> Match incoming records by key and choose which rows to update, insert or remove.

**Applies to:** Embedded ✅, Enterprise ✅

Early access in Enterprise

Use merge insert when incoming records should be compared with existing rows.
Combine update and insert policies for an **upsert**. Use [append](https://docs.lancedb.com/ingestion/insert.md)
when every incoming row should simply be added.

## Merge incoming rows by key

Merging is different from updating because it involves comparing incoming rows to existing rows by key, and then choosing what to do based on whether the key exists in the target table or not.
Use `merge_insert("id")` in Python or the corresponding SDK builder.

In merge operations, rows are split into three groups:

* **Matched**: key exists in both source and target.
* **Not matched**: key exists only in source.
* **Not matched by source**: key exists only in target.

Conditional merge clauses can compare old and new values. Use the `target.` prefix for the
existing table row and `source.` for the incoming row, for example
`target.last_update < source.last_update`.

Choose a key that identifies the same record in the source and target. Check
for duplicate and null keys before merging. A primary-key declaration does not
enforce uniqueness on ordinary writes, and append operations do not deduplicate
records for you.

A scalar index on the key can help matching. Keep it current and measure the
actual workload rather than assuming every merge uses an index. Enterprise may
reject a merge when the unindexed scan exceeds its configured limit; inspect
that error and the index state before retrying.

## Choose a merge policy

| Source and target relationship | Available action |
| - | - |
| Key appears in both | Update matching target rows, optionally with a condition. |
| Key appears only in the source | Insert it, or leave it out. |
| Key appears only in the target | Keep it, or explicitly delete it under a synchronization policy. |

The following examples are independent. Each starts from the table shown in
its code, so the expected results do not depend on running the previous example.
Python blocks include their own temporary connection. For a full TypeScript or
Rust program, see [Complete write workflow](#complete-example).

### Update matched rows only

This updates keys that already exist in the target table. Source rows with new keys are ignored.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
import pyarrow as pa

table = db.create_table(
    "users_example",
    data=pa.table(
        {
            "id": [1, 2],
            "name": ["Alice", "Bob"],
            "login_count": [10, 20],
        }
    ),
    mode="overwrite",
)

incoming_users = pa.table(
    {
        "id": [2, 3],
        "name": ["Bobby", "Charlie"],
        "login_count": [21, 5],
    }
)

(table.merge_insert("id").when_matched_update_all().execute(incoming_users))

print("Rows:", table.count_rows())
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
const table = await db.createTable(
  "users_example",
  [
    { id: 1, name: "Alice", login_count: 10 },
    { id: 2, name: "Bob", login_count: 20 },
  ],
  { mode: "overwrite" },
);

const incomingUsers = [
  { id: 2, name: "Bobby", login_count: 21 },
  { id: 3, name: "Charlie", login_count: 5 },
];

await table
  .mergeInsert("id")
  .whenMatchedUpdateAll()
  .execute(incomingUsers);
```

```rust Rust icon="rust" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
let table = db
    .create_table(
        "users_example",
        make_users_reader(vec![1, 2], vec!["Alice", "Bob"], Some(vec![10, 20])),
    )
    .mode(CreateTableMode::Overwrite)
    .execute()
    .await
    .unwrap();

let mut merge_insert = table.merge_insert(&["id"]);
merge_insert.when_matched_update_all(None);
merge_insert
    .execute(make_users_reader(
        vec![2, 3],
        vec!["Bobby", "Charlie"],
        Some(vec![21, 5]),
    ))
    .await
    .unwrap();
```

Expected table contents:

| id | name | login\_count |
| - | - | - |
| 1 | Alice | 10 |
| 2 | Bobby | 21 |

### Insert unmatched rows only

This inserts only brand-new keys from the source. Existing keys are left unchanged.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
import pyarrow as pa

table = db.create_table(
    "users_example",
    data=pa.table(
        {
            "id": [1, 2],
            "name": ["Alice", "Bob"],
            "login_count": [10, 20],
        }
    ),
    mode="overwrite",
)

incoming_users = pa.table(
    {
        "id": [2, 3],
        "name": ["Bobby", "Charlie"],
        "login_count": [21, 5],
    }
)

(table.merge_insert("id").when_not_matched_insert_all().execute(incoming_users))

print("Rows:", table.count_rows())
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
const table = await db.createTable(
  "users_example",
  [
    { id: 1, name: "Alice", login_count: 10 },
    { id: 2, name: "Bob", login_count: 20 },
  ],
  { mode: "overwrite" },
);

const incomingUsers = [
  { id: 2, name: "Bobby", login_count: 21 },
  { id: 3, name: "Charlie", login_count: 5 },
];

await table
  .mergeInsert("id")
  .whenNotMatchedInsertAll()
  .execute(incomingUsers);
```

```rust Rust icon="rust" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
let table = db
    .create_table(
        "users_example",
        make_users_reader(vec![1, 2], vec!["Alice", "Bob"], Some(vec![10, 20])),
    )
    .mode(CreateTableMode::Overwrite)
    .execute()
    .await
    .unwrap();

let mut merge_insert = table.merge_insert(&["id"]);
merge_insert.when_not_matched_insert_all();
merge_insert
    .execute(make_users_reader(
        vec![2, 3],
        vec!["Bobby", "Charlie"],
        Some(vec![21, 5]),
    ))
    .await
    .unwrap();
```

Expected table contents:

| id | name | login\_count |
| - | - | - |
| 1 | Alice | 10 |
| 2 | Bob | 20 |
| 3 | Charlie | 5 |

### Update matched rows and insert unmatched rows

Use both `when_matched_update_all()` and `when_not_matched_insert_all()` when you want to update existing keys and insert missing keys in one operation.

> [!NOTE]
>
> This is a conventional **upsert**.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
import pyarrow as pa

table = db.create_table(
    "users_example",
    data=pa.table(
        {
            "id": [1, 2],
            "name": ["Alice", "Bob"],
            "login_count": [10, 20],
        }
    ),
    mode="overwrite",
)

incoming_users = pa.table(
    {
        "id": [2, 3],
        "name": ["Bobby", "Charlie"],
        "login_count": [21, 5],
    }
)

(
    table.merge_insert("id")
    .when_matched_update_all()
    .when_not_matched_insert_all()
    .execute(incoming_users)
)

print("Rows:", table.count_rows())
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
const table = await db.createTable(
  "users_example",
  [
    { id: 1, name: "Alice", login_count: 10 },
    { id: 2, name: "Bob", login_count: 20 },
  ],
  { mode: "overwrite" },
);

const incomingUsers = [
  { id: 2, name: "Bobby", login_count: 21 },
  { id: 3, name: "Charlie", login_count: 5 },
];

await table
  .mergeInsert("id")
  .whenMatchedUpdateAll()
  .whenNotMatchedInsertAll()
  .execute(incomingUsers);
```

```rust Rust icon="rust" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
let table = db
    .create_table(
        "users_example",
        make_users_reader(vec![1, 2], vec!["Alice", "Bob"], Some(vec![10, 20])),
    )
    .mode(CreateTableMode::Overwrite)
    .execute()
    .await
    .unwrap();

let mut merge_insert = table.merge_insert(&["id"]);
merge_insert
    .when_matched_update_all(None)
    .when_not_matched_insert_all();
merge_insert
    .execute(make_users_reader(
        vec![2, 3],
        vec!["Bobby", "Charlie"],
        Some(vec![21, 5]),
    ))
    .await
    .unwrap();
```

Expected table contents:

| id | name | login\_count |
| - | - | - |
| 1 | Alice | 10 |
| 2 | Bobby | 21 |
| 3 | Charlie | 5 |

### Delete target rows that are missing from source

Use `when_not_matched_by_source_delete()` only when the incoming data is the
complete set for the target scope you intend to synchronize. An incremental
batch is usually not a complete source: deleting everything absent from it
could remove unrelated target records. Inspect the unmatched target keys first.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
import pyarrow as pa

table = db.create_table(
    "users_example",
    data=pa.table(
        {
            "id": [1, 2, 3],
            "name": ["Alice", "Bob", "Charlie"],
            "login_count": [10, 20, 5],
        }
    ),
    mode="overwrite",
)

incoming_users = pa.table(
    {
        "id": [2, 3],
        "name": ["Bobby", "Charlie"],
        "login_count": [21, 5],
    }
)

(
    table.merge_insert("id")
    .when_matched_update_all()
    .when_not_matched_insert_all()
    .when_not_matched_by_source_delete()
    .execute(incoming_users)
)

print("Rows:", table.count_rows())
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
const table = await db.createTable(
  "users_example",
  [
    { id: 1, name: "Alice", login_count: 10 },
    { id: 2, name: "Bob", login_count: 20 },
    { id: 3, name: "Charlie", login_count: 5 },
  ],
  { mode: "overwrite" },
);

const incomingUsers = [
  { id: 2, name: "Bobby", login_count: 21 },
  { id: 3, name: "Charlie", login_count: 5 },
];

await table
  .mergeInsert("id")
  .whenMatchedUpdateAll()
  .whenNotMatchedInsertAll()
  .whenNotMatchedBySourceDelete()
  .execute(incomingUsers);
```

```rust Rust icon="rust" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
let table = db
    .create_table(
        "users_example",
        make_users_reader(
            vec![1, 2, 3],
            vec!["Alice", "Bob", "Charlie"],
            Some(vec![10, 20, 5]),
        ),
    )
    .mode(CreateTableMode::Overwrite)
    .execute()
    .await
    .unwrap();

let mut merge_insert = table.merge_insert(&["id"]);
merge_insert
    .when_matched_update_all(None)
    .when_not_matched_insert_all()
    .when_not_matched_by_source_delete(None);
merge_insert
    .execute(make_users_reader(
        vec![2, 3],
        vec!["Bobby", "Charlie"],
        Some(vec![21, 5]),
    ))
    .await
    .unwrap();
```

Expected table contents:

| id | name | login\_count |
| - | - | - |
| 2 | Bobby | 21 |
| 3 | Charlie | 5 |

Here, IDs 2 and 3 are updated and ID 1 is deleted. This policy synchronizes
the target to the supplied source set.

### Use partial columns in merge updates

Merge updates do not require you to provide values for all columns. You can provide only a subset of columns in source rows. For matched rows, only the provided columns are updated.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
import pyarrow as pa

table = db.create_table(
    "users_example",
    data=pa.table(
        {
            "id": [1, 2],
            "name": ["Alice", "Bob"],
            "login_count": [10, 20],
        }
    ),
    mode="overwrite",
)

incoming_users = pa.table(
    {
        "id": [2, 3],
        "name": ["Bobby", "Charlie"],
    }
)

(
    table.merge_insert("id")
    .when_matched_update_all()
    .when_not_matched_insert_all()
    .execute(incoming_users)
)

print("Rows:", table.count_rows())
```

```typescript TypeScript icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
const table = await db.createTable(
  "users_example",
  [
    { id: 1, name: "Alice", login_count: 10 },
    { id: 2, name: "Bob", login_count: 20 },
  ],
  { mode: "overwrite" },
);

const incomingUsers = [
  { id: 2, name: "Bobby" },
  { id: 3, name: "Charlie" },
];

await table
  .mergeInsert("id")
  .whenMatchedUpdateAll()
  .whenNotMatchedInsertAll()
  .execute(incomingUsers);
```

```rust Rust icon="rust" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
let table = db
    .create_table(
        "users_example",
        make_users_reader(vec![1, 2], vec!["Alice", "Bob"], Some(vec![10, 20])),
    )
    .mode(CreateTableMode::Overwrite)
    .execute()
    .await
    .unwrap();

let mut merge_insert = table.merge_insert(&["id"]);
merge_insert
    .when_matched_update_all(None)
    .when_not_matched_insert_all();
merge_insert
    .execute(make_users_reader(vec![2, 3], vec!["Bobby", "Charlie"], None))
    .await
    .unwrap();
```

Expected table contents:

| id | name | login\_count |
| - | - | - |
| 1 | Alice | 10 |
| 2 | Bobby | 20 |
| 3 | Charlie | null |

Note that in the example above, when `merge_insert` creates a new row, any missing columns are written as `null`. If a missing column is non-nullable in your schema, the insert will fail.

## Check the merged records

Inspect the keys that should have changed, confirm that unrelated rows remain,
and check inserted or deleted counts when the SDK returns them. Repeating a
merge is only safe when its keys, conditions and update values produce the
intended result on retry.

### Embeddings and expression predicates

When a table uses an [embedding definition](https://docs.lancedb.com/embedding.md), behavior depends on
which source and vector fields the incoming records provide. Do not assume a
partial metadata update recomputes embeddings. Inspect the resulting vector
when changing the input used to generate it.

Rust's `when_matched_update_all_expr` and
`when_not_matched_by_source_delete_expr` accept DataFusion expressions for
LanceDB Embedded tables. Use the string predicate variants for Enterprise tables.

## Complete write workflow

> [accordion] Complete examples: create, append, update, upsert and delete
>
> Each program creates a temporary local database, performs the five operations,
> prints the result, and removes its temporary data when finished.
>
> * **Python:** install `lancedb`, save the code as `ingestion.py`, and run
>   `python ingestion.py`.
> * **TypeScript:** install `@lancedb/lancedb`, save the code as `ingestion.mts`,
>   and run `node ingestion.mts` with Node.js 22.18 or later.
> * **Rust:** download the [complete Rust project](https://docs.lancedb.com/static/code/ingestion-rust.zip),
>   extract it, and run `cargo run --locked`. It includes `src/main.rs`, the Cargo
>   manifest and a lockfile for the example's dependencies. The code is shown below.
>
> ```python ingestion.py icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
> from tempfile import TemporaryDirectory
>
> import lancedb
>
> with TemporaryDirectory(prefix="lancedb-ingestion-") as uri:
>     db = lancedb.connect(uri)
>     table = db.create_table(
>         "users",
>         [
>             {"id": 1, "name": "Alice", "login_count": 10},
>             {"id": 2, "name": "Bob", "login_count": 20},
>         ],
>     )
>     table.add([{"id": 3, "name": "Charlie", "login_count": 5}])
>     table.update(where="id = 2", values={"name": "Bobby"})
>     table.update(where="id = 2", values_sql={"login_count": "login_count + 1"})
>     (
>         table.merge_insert("id")
>         .when_matched_update_all()
>         .when_not_matched_insert_all()
>         .execute(
>             [
>                 {"id": 3, "name": "Charlie", "login_count": 6},
>                 {"id": 4, "name": "Dana", "login_count": 1},
>             ]
>         )
>     )
>     print("Rows selected for deletion:", table.search().where("id = 4").to_list())
>     table.delete("id = 4")
>     rows = sorted(table.search().to_list(), key=lambda row: row["id"])
>     print(rows)
> ```
>
> ```typescript ingestion.mts icon="square-js" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
> const lancedb = await import("@lancedb/lancedb");
> const { mkdtemp, rm } = await import("node:fs/promises");
> const { tmpdir } = await import("node:os");
> const { join } = await import("node:path");
>
> const uri = await mkdtemp(join(tmpdir(), "lancedb-ingestion-"));
> try {
>   const db = await lancedb.connect(uri);
>   const table = await db.createTable("users", [
>     { id: 1, name: "Alice", login_count: 10 },
>     { id: 2, name: "Bob", login_count: 20 },
>   ]);
>   await table.add([{ id: 3, name: "Charlie", login_count: 5 }]);
>   await table.update({ where: "id = 2", values: { name: "Bobby" } });
>   await table.update({
>     where: "id = 2",
>     valuesSql: { login_count: "login_count + 1" },
>   });
>   await table
>     .mergeInsert("id")
>     .whenMatchedUpdateAll()
>     .whenNotMatchedInsertAll()
>     .execute([
>       { id: 3, name: "Charlie", login_count: 6 },
>       { id: 4, name: "Dana", login_count: 1 },
>     ]);
>   console.log(
>     "Rows selected for deletion:",
>     await table.query().where("id = 4").toArray(),
>   );
>   await table.delete("id = 4");
>   const rows = (await table.query().toArray()).sort((a, b) => a.id - b.id);
>   console.log(rows);
> } finally {
>   await rm(uri, { recursive: true, force: true });
> }
> ```
>
> ```rust src/main.rs icon="rust" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
> use std::sync::Arc;
>
> use arrow_array::{Int64Array, RecordBatch, RecordBatchIterator, RecordBatchReader, StringArray};
> use arrow_schema::{DataType, Field, Schema};
> use futures::TryStreamExt;
> use lancedb::query::{ExecutableQuery, QueryBase};
>
> fn make_users_reader(rows: &[(i64, &str, i64)]) -> Box<dyn RecordBatchReader + Send> {
>     let schema = Arc::new(Schema::new(vec![
>         Field::new("id", DataType::Int64, false),
>         Field::new("name", DataType::Utf8, false),
>         Field::new("login_count", DataType::Int64, false),
>     ]));
>     let batch = RecordBatch::try_new(
>         schema.clone(),
>         vec![
>             Arc::new(Int64Array::from_iter_values(rows.iter().map(|r| r.0))),
>             Arc::new(StringArray::from_iter_values(rows.iter().map(|r| r.1))),
>             Arc::new(Int64Array::from_iter_values(rows.iter().map(|r| r.2))),
>         ],
>     )
>     .unwrap();
>     Box::new(RecordBatchIterator::new(
>         vec![Ok(batch)].into_iter(),
>         schema,
>     ))
> }
>
> #[tokio::main]
> async fn main() -> Result<(), Box<dyn std::error::Error>> {
>     let directory = tempfile::tempdir()?;
>     let db = lancedb::connect(directory.path().to_str().unwrap())
>         .execute()
>         .await?;
>     let table = db
>         .create_table(
>             "users",
>             make_users_reader(&[(1, "Alice", 10), (2, "Bob", 20)]),
>         )
>         .execute()
>         .await?;
>     table
>         .add(make_users_reader(&[(3, "Charlie", 5)]))
>         .execute()
>         .await?;
>     table
>         .update()
>         .only_if("id = 2")
>         .column("name", "'Bobby'")
>         .execute()
>         .await?;
>     table
>         .update()
>         .only_if("id = 2")
>         .column("login_count", "login_count + 1")
>         .execute()
>         .await?;
>     let mut merge = table.merge_insert(&["id"]);
>     merge
>         .when_matched_update_all(None)
>         .when_not_matched_insert_all();
>     merge
>         .execute(make_users_reader(&[(3, "Charlie", 6), (4, "Dana", 1)]))
>         .await?;
>     let selected: Vec<RecordBatch> = table
>         .query()
>         .only_if("id = 4")
>         .execute()
>         .await?
>         .try_collect()
>         .await?;
>     println!("Rows selected for deletion: {selected:?}");
>     table.delete("id = 4").await?;
>     let rows: Vec<RecordBatch> = table.query().execute().await?.try_collect().await?;
>     println!("Remaining rows: {rows:?}");
>     assert_eq!(table.count_rows(None).await?, 3);
>     assert_eq!(
>         table
>             .count_rows(Some(
>                 "id = 2 AND name = 'Bobby' AND login_count = 21".into()
>             ))
>             .await?,
>         1
>     );
>     assert_eq!(
>         table
>             .count_rows(Some("id = 3 AND login_count = 6".into()))
>             .await?,
>         1
>     );
>     Ok(())
> }
> ```
>
> After the operations, Alice has 10 logins, Bobby has 21, and Charlie has 6.
> Dana is selected for deletion and is absent from the final result.


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