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

# Updating and Modifying Table Data

> Learn how to update, merge, and delete rows in a LanceDB table.

export const RsUpdateOptimizeCleanup = "table\n    .optimize(OptimizeAction::Prune {\n        older_than: Some(Duration::days(1)),\n        delete_unverified: None,\n        error_if_tagged_old_versions: None,\n    })\n    .await\n    .unwrap();\n";

export const RsDeleteOperation = "// delete data\nlet predicate = \"id = 3\";\ntable.delete(predicate).await.unwrap();\n";

export const RsMergePartialColumns = "let table = db\n    .create_table(\n        \"users_example\",\n        make_users_reader(vec![1, 2], vec![\"Alice\", \"Bob\"], Some(vec![10, 20])),\n    )\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\n\nlet mut merge_insert = table.merge_insert(&[\"id\"]);\nmerge_insert\n    .when_matched_update_all(None)\n    .when_not_matched_insert_all();\nmerge_insert\n    .execute(make_users_reader(vec![2, 3], vec![\"Bobby\", \"Charlie\"], None))\n    .await\n    .unwrap();\n";

export const RsMergeDeleteMissingBySource = "let table = db\n    .create_table(\n        \"users_example\",\n        make_users_reader(\n            vec![1, 2, 3],\n            vec![\"Alice\", \"Bob\", \"Charlie\"],\n            Some(vec![10, 20, 5]),\n        ),\n    )\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\n\nlet mut merge_insert = table.merge_insert(&[\"id\"]);\nmerge_insert\n    .when_matched_update_all(None)\n    .when_not_matched_insert_all()\n    .when_not_matched_by_source_delete(None);\nmerge_insert\n    .execute(make_users_reader(\n        vec![2, 3],\n        vec![\"Bobby\", \"Charlie\"],\n        Some(vec![21, 5]),\n    ))\n    .await\n    .unwrap();\n";

export const RsMergeUpdateInsert = "let table = db\n    .create_table(\n        \"users_example\",\n        make_users_reader(vec![1, 2], vec![\"Alice\", \"Bob\"], Some(vec![10, 20])),\n    )\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\n\nlet mut merge_insert = table.merge_insert(&[\"id\"]);\nmerge_insert\n    .when_matched_update_all(None)\n    .when_not_matched_insert_all();\nmerge_insert\n    .execute(make_users_reader(\n        vec![2, 3],\n        vec![\"Bobby\", \"Charlie\"],\n        Some(vec![21, 5]),\n    ))\n    .await\n    .unwrap();\n";

export const RsInsertIfNotExists = "let table = db\n    .create_table(\n        \"users_example\",\n        make_users_reader(vec![1, 2], vec![\"Alice\", \"Bob\"], Some(vec![10, 20])),\n    )\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\n\nlet mut merge_insert = table.merge_insert(&[\"id\"]);\nmerge_insert.when_not_matched_insert_all();\nmerge_insert\n    .execute(make_users_reader(\n        vec![2, 3],\n        vec![\"Bobby\", \"Charlie\"],\n        Some(vec![21, 5]),\n    ))\n    .await\n    .unwrap();\n";

export const RsMergeMatchedUpdateOnly = "let table = db\n    .create_table(\n        \"users_example\",\n        make_users_reader(vec![1, 2], vec![\"Alice\", \"Bob\"], Some(vec![10, 20])),\n    )\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\n\nlet mut merge_insert = table.merge_insert(&[\"id\"]);\nmerge_insert.when_matched_update_all(None);\nmerge_insert\n    .execute(make_users_reader(\n        vec![2, 3],\n        vec![\"Bobby\", \"Charlie\"],\n        Some(vec![21, 5]),\n    ))\n    .await\n    .unwrap();\n";

export const RsUpdateUsingSql = "let table = db\n    .create_table(\n        \"users_example\",\n        make_users_reader(vec![1, 2], vec![\"Alice\", \"Bob\"], Some(vec![10, 20])),\n    )\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\ntable\n    .update()\n    .only_if(\"id = 2\")\n    .column(\"login_count\", \"login_count + 1\")\n    .execute()\n    .await\n    .unwrap();\n";

export const RsUpdateOperation = "let table = db\n    .create_table(\n        \"users_example\",\n        make_users_reader(vec![1, 2], vec![\"Alice\", \"Bob\"], Some(vec![10, 20])),\n    )\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\ntable\n    .update()\n    .only_if(\"id = 2\")\n    .column(\"name\", \"'Bobby'\")\n    .execute()\n    .await\n    .unwrap();\n";

export const RsUpdateExampleTableSetup = "let table = db\n    .create_table(\n        \"users_example\",\n        make_users_reader(vec![1, 2], vec![\"Alice\", \"Bob\"], Some(vec![10, 20])),\n    )\n    .mode(CreateTableMode::Overwrite)\n    .execute()\n    .await\n    .unwrap();\n";

export const RsUpdateConnectLocal = "let db = connect(\"./data\").execute().await.unwrap();\n";

export const RsUpdateConnectEnterprise = "let uri = \"db://your-project-slug\";\nlet api_key = \"your-api-key\";\nlet region = \"us-east-1\";\n";

export const TsUpdateOptimizeCleanup = "const olderThan = new Date();\nolderThan.setDate(olderThan.getDate() - 1);\nawait table.optimize({ cleanupOlderThan: olderThan });\n";

export const TsDeleteOperation = "// delete data\nconst predicate = \"id = 3\";\nawait table.delete(predicate);\n";

export const TsMergePartialColumns = "const table = await db.createTable(\n  \"users_example\",\n  [\n    { id: 1, name: \"Alice\", login_count: 10 },\n    { id: 2, name: \"Bob\", login_count: 20 },\n  ],\n  { mode: \"overwrite\" },\n);\n\nconst incomingUsers = [\n  { id: 2, name: \"Bobby\" },\n  { id: 3, name: \"Charlie\" },\n];\n\nawait table\n  .mergeInsert(\"id\")\n  .whenMatchedUpdateAll()\n  .whenNotMatchedInsertAll()\n  .execute(incomingUsers);\n";

export const TsMergeDeleteMissingBySource = "const table = await db.createTable(\n  \"users_example\",\n  [\n    { id: 1, name: \"Alice\", login_count: 10 },\n    { id: 2, name: \"Bob\", login_count: 20 },\n    { id: 3, name: \"Charlie\", login_count: 5 },\n  ],\n  { mode: \"overwrite\" },\n);\n\nconst incomingUsers = [\n  { id: 2, name: \"Bobby\", login_count: 21 },\n  { id: 3, name: \"Charlie\", login_count: 5 },\n];\n\nawait table\n  .mergeInsert(\"id\")\n  .whenMatchedUpdateAll()\n  .whenNotMatchedInsertAll()\n  .whenNotMatchedBySourceDelete()\n  .execute(incomingUsers);\n";

export const TsMergeUpdateInsert = "const table = await db.createTable(\n  \"users_example\",\n  [\n    { id: 1, name: \"Alice\", login_count: 10 },\n    { id: 2, name: \"Bob\", login_count: 20 },\n  ],\n  { mode: \"overwrite\" },\n);\n\nconst incomingUsers = [\n  { id: 2, name: \"Bobby\", login_count: 21 },\n  { id: 3, name: \"Charlie\", login_count: 5 },\n];\n\nawait table\n  .mergeInsert(\"id\")\n  .whenMatchedUpdateAll()\n  .whenNotMatchedInsertAll()\n  .execute(incomingUsers);\n";

export const TsInsertIfNotExists = "const table = await db.createTable(\n  \"users_example\",\n  [\n    { id: 1, name: \"Alice\", login_count: 10 },\n    { id: 2, name: \"Bob\", login_count: 20 },\n  ],\n  { mode: \"overwrite\" },\n);\n\nconst incomingUsers = [\n  { id: 2, name: \"Bobby\", login_count: 21 },\n  { id: 3, name: \"Charlie\", login_count: 5 },\n];\n\nawait table\n  .mergeInsert(\"id\")\n  .whenNotMatchedInsertAll()\n  .execute(incomingUsers);\n";

export const TsMergeMatchedUpdateOnly = "const table = await db.createTable(\n  \"users_example\",\n  [\n    { id: 1, name: \"Alice\", login_count: 10 },\n    { id: 2, name: \"Bob\", login_count: 20 },\n  ],\n  { mode: \"overwrite\" },\n);\n\nconst incomingUsers = [\n  { id: 2, name: \"Bobby\", login_count: 21 },\n  { id: 3, name: \"Charlie\", login_count: 5 },\n];\n\nawait table\n  .mergeInsert(\"id\")\n  .whenMatchedUpdateAll()\n  .execute(incomingUsers);\n";

export const TsUpdateUsingSql = "const table = await db.createTable(\n  \"users_example\",\n  [\n    { id: 1, name: \"Alice\", login_count: 10 },\n    { id: 2, name: \"Bob\", login_count: 20 },\n  ],\n  { mode: \"overwrite\" },\n);\nawait table.update({\n  where: \"id = 2\",\n  valuesSql: { login_count: \"login_count + 1\" },\n});\n";

export const TsUpdateOperation = "const table = await db.createTable(\n  \"users_example\",\n  [\n    { id: 1, name: \"Alice\", login_count: 10 },\n    { id: 2, name: \"Bob\", login_count: 20 },\n  ],\n  { mode: \"overwrite\" },\n);\nawait table.update({ where: \"id = 2\", values: { name: \"Bobby\" } });\n";

export const TsUpdateExampleTableSetup = "const table = await db.createTable(\n  \"users_example\",\n  [\n    { id: 1, name: \"Alice\", login_count: 10 },\n    { id: 2, name: \"Bob\", login_count: 20 },\n  ],\n  { mode: \"overwrite\" },\n);\n";

export const TsUpdateConnectLocal = "const db = await lancedb.connect(\"./data\");\n";

export const TsUpdateConnectEnterprise = "const db = await lancedb.connect(\"db://your-project-slug\", {\n  apiKey: \"your-api-key\",\n  region: \"us-east-1\",\n});\n";

export const UpdateOptimizeCleanup = "from datetime import timedelta\n\ntable.optimize(cleanup_older_than=timedelta(days=1))\n";

export const DeleteOperation = "# delete data\npredicate = \"id = 3\"\ntable.delete(predicate)\n";

export const MergePartialColumns = "import pyarrow as pa\n\ntable = db.create_table(\n    \"users_example\",\n    data=pa.table(\n        {\n            \"id\": [1, 2],\n            \"name\": [\"Alice\", \"Bob\"],\n            \"login_count\": [10, 20],\n        }\n    ),\n    mode=\"overwrite\",\n)\n\nincoming_users = pa.table(\n    {\n        \"id\": [2, 3],\n        \"name\": [\"Bobby\", \"Charlie\"],\n    }\n)\n\n(\n    table.merge_insert(\"id\")\n    .when_matched_update_all()\n    .when_not_matched_insert_all()\n    .execute(incoming_users)\n)\n";

export const MergeDeleteMissingBySource = "import pyarrow as pa\n\ntable = db.create_table(\n    \"users_example\",\n    data=pa.table(\n        {\n            \"id\": [1, 2, 3],\n            \"name\": [\"Alice\", \"Bob\", \"Charlie\"],\n            \"login_count\": [10, 20, 5],\n        }\n    ),\n    mode=\"overwrite\",\n)\n\nincoming_users = pa.table(\n    {\n        \"id\": [2, 3],\n        \"name\": [\"Bobby\", \"Charlie\"],\n        \"login_count\": [21, 5],\n    }\n)\n\n(\n    table.merge_insert(\"id\")\n    .when_matched_update_all()\n    .when_not_matched_insert_all()\n    .when_not_matched_by_source_delete()\n    .execute(incoming_users)\n)\n";

export const MergeUpdateInsert = "import pyarrow as pa\n\ntable = db.create_table(\n    \"users_example\",\n    data=pa.table(\n        {\n            \"id\": [1, 2],\n            \"name\": [\"Alice\", \"Bob\"],\n            \"login_count\": [10, 20],\n        }\n    ),\n    mode=\"overwrite\",\n)\n\nincoming_users = pa.table(\n    {\n        \"id\": [2, 3],\n        \"name\": [\"Bobby\", \"Charlie\"],\n        \"login_count\": [21, 5],\n    }\n)\n\n(\n    table.merge_insert(\"id\")\n    .when_matched_update_all()\n    .when_not_matched_insert_all()\n    .execute(incoming_users)\n)\n";

export const InsertIfNotExists = "import pyarrow as pa\n\ntable = db.create_table(\n    \"users_example\",\n    data=pa.table(\n        {\n            \"id\": [1, 2],\n            \"name\": [\"Alice\", \"Bob\"],\n            \"login_count\": [10, 20],\n        }\n    ),\n    mode=\"overwrite\",\n)\n\nincoming_users = pa.table(\n    {\n        \"id\": [2, 3],\n        \"name\": [\"Bobby\", \"Charlie\"],\n        \"login_count\": [21, 5],\n    }\n)\n\n(table.merge_insert(\"id\").when_not_matched_insert_all().execute(incoming_users))\n";

export const MergeMatchedUpdateOnly = "import pyarrow as pa\n\ntable = db.create_table(\n    \"users_example\",\n    data=pa.table(\n        {\n            \"id\": [1, 2],\n            \"name\": [\"Alice\", \"Bob\"],\n            \"login_count\": [10, 20],\n        }\n    ),\n    mode=\"overwrite\",\n)\n\nincoming_users = pa.table(\n    {\n        \"id\": [2, 3],\n        \"name\": [\"Bobby\", \"Charlie\"],\n        \"login_count\": [21, 5],\n    }\n)\n\n(table.merge_insert(\"id\").when_matched_update_all().execute(incoming_users))\n";

export const UpdateUsingSql = "import pyarrow as pa\n\ntable = db.create_table(\n    \"users_example\",\n    data=pa.table(\n        {\n            \"id\": [1, 2],\n            \"name\": [\"Alice\", \"Bob\"],\n            \"login_count\": [10, 20],\n        }\n    ),\n    mode=\"overwrite\",\n)\ntable.update(where=\"id = 2\", values_sql={\"login_count\": \"login_count + 1\"})\n";

export const UpdateOperation = "import pyarrow as pa\n\ntable = db.create_table(\n    \"users_example\",\n    data=pa.table(\n        {\n            \"id\": [1, 2],\n            \"name\": [\"Alice\", \"Bob\"],\n            \"login_count\": [10, 20],\n        }\n    ),\n    mode=\"overwrite\",\n)\ntable.update(where=\"id = 2\", values={\"name\": \"Bobby\"})\n";

export const UpdateExampleTableSetup = "import pyarrow as pa\n\ntable = db.create_table(\n    \"users_example\",\n    data=pa.table(\n        {\n            \"id\": [1, 2],\n            \"name\": [\"Alice\", \"Bob\"],\n            \"login_count\": [10, 20],\n        }\n    ),\n    mode=\"overwrite\",\n)\n";

export const UpdateConnectLocal = "import lancedb\n\ndb = lancedb.connect(\"./data\")\n";

export const UpdateConnectEnterprise = "import lancedb\n\ndb = lancedb.connect(\n    uri=\"db://your-project-slug\",\n    api_key=\"your-api-key\",\n    region=\"us-east-1\",\n)\n";

Updating or modifying data involves changing rows in an existing table.
LanceDB provides two families of write operations that can modify data in a table:

* `update(...)`: mutate existing rows that match a SQL filter.
* `merge_insert(...)`: compare incoming rows to existing rows by key, then choose what to do for each case.

The `update` method is simpler to use when you already know which rows you want to modify and you do not need to compare against an incoming dataset. The `merge_insert` method is more powerful when you have a new dataset that you want to merge into an existing table, and you want LanceDB to handle the logic of comparing against existing rows by key.

Let's look at an example that demonstrates these operations in practice.

## Connect to LanceDB

Connect to your local LanceDB instance:

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {UpdateConnectLocal}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsUpdateConnectLocal}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsUpdateConnectLocal}
  </CodeBlock>
</CodeGroup>

Or, connect to LanceDB Enterprise:

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {UpdateConnectEnterprise}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsUpdateConnectEnterprise}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsUpdateConnectEnterprise}
  </CodeBlock>
</CodeGroup>

<Expandable title="In the Rust snippets, a `make_users_reader` helper is used  to build Arrow input data.">
  <CodeGroup>
    <CodeBlock filename="Rust" language="Rust" icon="rust">
      {RsUpdateMakeUsersReader}
    </CodeBlock>
  </CodeGroup>
</Expandable>

## Create the example table

We'll start by creating a simple table with `id`, `name`, and `login_count` columns. All examples below use the same table.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {UpdateExampleTableSetup}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsUpdateExampleTableSetup}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsUpdateExampleTableSetup}
  </CodeBlock>
</CodeGroup>

Expected table contents:

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

The example above shows a PyArrow schema. You can just as well create the table using other
table creation patterns (Pandas, Polars, Pydantic, iterators, etc.) -- see the [ingestion](/tables/create/) guide for more details.

## Choose a write method

| Family         | Method                                                          | Use this when...                                                                                                                    |
| -------------- | --------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------- |
| `update`       | `update(where=..., values=...)`                                 | You want to edit rows that already exist, using a SQL filter.                                                                       |
| `merge_insert` | `.when_matched_update_all()`                                    | You have incoming rows and want to update keys that already exist in the table.                                                     |
| `merge_insert` | `.when_not_matched_insert_all()`                                | You have incoming rows and want to insert keys that do not exist yet.                                                               |
| `merge_insert` | `.when_matched_update_all()` + `.when_not_matched_insert_all()` | You want both behaviors together (often called **upsert**: update existing keys **and** insert missing keys in the same operation). |
| `merge_insert` | `.when_not_matched_by_source_delete(...)`                       | You want to remove target rows that are missing from the incoming source set.                                                       |

## Update rows

Use `update` when you already know which target rows to modify and you do not need to compare against an incoming dataset.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {UpdateOperation}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsUpdateOperation}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsUpdateOperation}
  </CodeBlock>
</CodeGroup>

Expected table contents:

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

<Warning title="Warning">
  Updating nested columns is not yet supported.
</Warning>

## Update rows with SQL expressions

Use `values_sql` when you want to use SQL-like expressions to update rows. This is useful for operations like incrementing a counter, or setting a column value based on another column.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {UpdateUsingSql}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsUpdateUsingSql}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsUpdateUsingSql}
  </CodeBlock>
</CodeGroup>

Expected table contents:

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

<Note title="SQL syntax">
  See the [SQL queries](/search/sql/) page for more information on the supported SQL syntax.
</Note>

When rows are updated, they are moved out of any existing index. The row will still show up in search queries, but the query will not be as fast as it would be if the row was in the index. If you update a large proportion of rows, consider triggering an index rebuild afterwards.

## 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.
The `merge_insert(""..."")` method lets you do this.

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.

<Tip>
  **Use scalar indexes to speed up merge insert**

  The merge insert command performs a join between the input data and the target table `on` the key you provide. This requires scanning that entire column, which can be expensive for large tables. To speed up this operation, create a scalar index on the join column, which will allow LanceDB to find matches without scanning the whole table.

  Read more about scalar indices in the [Scalar Index](/indexing/scalar-index/) guide.
</Tip>

<Info>
  If you see this HTTP 400 error from `merge_insert`: `Bad request: Merge insert cannot be performed because the number of unindexed rows exceeds the maximum of 10000`. Verify that the scalar index on the join column is up to date before retrying.
</Info>

Like the create table and add APIs, the merge insert API will automatically compute embeddings based on the [embedding registry](/embedding/index#embedding-registry) if the table has an embedding definition in its schema.

During `merge_insert`, if the input data doesn't contain the source column (i.e., the original field used to generate embeddings, such as text for a text embedding model or `image_uri` for an image model), or if a vector value is already provided, LanceDB skips embedding generation for that row. Embeddings are only auto-generated when that source field is present in the incoming data, **and** the vector field is empty.

### Update matched rows only

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

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {MergeMatchedUpdateOnly}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsMergeMatchedUpdateOnly}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsMergeMatchedUpdateOnly}
  </CodeBlock>
</CodeGroup>

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.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {InsertIfNotExists}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsInsertIfNotExists}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsInsertIfNotExists}
  </CodeBlock>
</CodeGroup>

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.

<Info>
  This is a conventional **upsert**.
</Info>

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {MergeUpdateInsert}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsMergeUpdateInsert}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsMergeUpdateInsert}
  </CodeBlock>
</CodeGroup>

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()` when you want to remove any target row that does not appear in the incoming source data.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {MergeDeleteMissingBySource}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsMergeDeleteMissingBySource}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsMergeDeleteMissingBySource}
  </CodeBlock>
</CodeGroup>

Expected table contents:

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

In the example above, LanceDB matches rows by `id`. Rows with `id=2` and `id=3` exist in both the table and incoming data, so they are updated. Row `id=1` exists only in the target, so it is deleted.

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

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {MergePartialColumns}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsMergePartialColumns}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsMergePartialColumns}
  </CodeBlock>
</CodeGroup>

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.

## Delete rows

Delete operations **soft delete** rows that match a given condition.
The underlying data is not immediately removed, but is marked
for deletion (in the [deletion files](https://lance.org/format/table/#deletion-files) at the Lance format level) and excluded from query results.

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {DeleteOperation}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsDeleteOperation}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsDeleteOperation}
  </CodeBlock>
</CodeGroup>

Expected table contents:

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

<Warning>
  **Deleting rows removes them from the index**

  When rows are deleted, those rows are also excluded from the index segments, so indexed queries will not return them either. If ALL the rows are deleted (i.e., the table is emptied), ensure that you recreate the index after ingesting new data.
</Warning>

To permanently remove deleted rows, you can optimize the table, which will run compaction and cleans up the soft-deleted rows, which frees up storage space.

* In LanceDB OSS, compaction and cleanup are manual. Run `table.optimize()` regularly to free up disk space.
* In LanceDB Enterprise, files aren't cleaned up by default. You can configure automatic compaction and cleanup behavior at cluster setup time to suit your organization's retention policy.

By default, table cleanup removes data up to 7 days ago. If you need to reclaim space from deleted rows more aggressively, manually call `table.optimize()` use a shorter retention window as follows:

<CodeGroup>
  <CodeBlock filename="Python" language="Python" icon="python">
    {UpdateOptimizeCleanup}
  </CodeBlock>

  <CodeBlock filename="TypeScript" language="TypeScript" icon="square-js">
    {TsUpdateOptimizeCleanup}
  </CodeBlock>

  <CodeBlock filename="Rust" language="Rust" icon="rust">
    {RsUpdateOptimizeCleanup}
  </CodeBlock>
</CodeGroup>