Merge

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

Update matched rows only

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

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())

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);

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.

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())

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);

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.

This is a conventional upsert.

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())

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);

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.

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())

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);

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.

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())

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);

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, 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

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

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)

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 });
}

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.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Merge incoming rows by key

Choose a merge policy

Update matched rows only

Insert unmatched rows only

Update matched rows and insert unmatched rows

Delete target rows that are missing from source

Use partial columns in merge updates

Check the merged records

Embeddings and expression predicates

Complete write workflow

​Merge incoming rows by key

​Choose a merge policy

​Update matched rows only

​Insert unmatched rows only

​Update matched rows and insert unmatched rows

​Delete target rows that are missing from source

​Use partial columns in merge updates

​Check the merged records

​Embeddings and expression predicates

​Complete write workflow

Merge incoming rows by key

Choose a merge policy

Update matched rows only

Insert unmatched rows only

Update matched rows and insert unmatched rows

Delete target rows that are missing from source

Use partial columns in merge updates

Check the merged records

Embeddings and expression predicates

Complete write workflow