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

# Delete rows

> Select and delete records, verify the remaining rows, and understand storage retention.

Deleting rows removes matching records from the current table contents. It
keeps the table and schema so you can continue reading and writing. To remove
the table itself, see [Drop a table](https://docs.lancedb.com/enterprise/v1-14-0/ingestion/create-table.md#drop-a-table).

## Inspect the rows you intend to delete

Use the same predicate for inspection and deletion. The example below creates
Alice, Bob and Charlie, prints Charlie's row, and deletes it by `id = 3`.
Inspection and deletion are separate operations; concurrent writes can change
which rows match between them.

## Delete matching records

Each program creates Alice, Bob and Charlie in a temporary local database,
deletes Charlie, and prints the remaining rows: **Alice and Bob**. The temporary
database is removed when the program finishes.

* **Python:** install `lancedb`, save the code as `delete_rows.py`, and run
  `python delete_rows.py`.
* **TypeScript:** install `@lancedb/lancedb`, save the code as `delete_rows.mts`,
  and run `node delete_rows.mts` with Node.js 22.18 or later.
* **Rust:** download the [Rust examples](https://raw.githubusercontent.com/lancedb/lancedb/2af85bea8999c8cd23ec01ff62ba110c54652c10/docs/web/static/code/ingestion-feedback-rust.zip),
  extract the project, and run `cargo run --locked --bin delete`.

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
table = db.create_table(
    "users",
    [
        {"id": 1, "name": "Alice"},
        {"id": 2, "name": "Bob"},
        {"id": 3, "name": "Charlie"},
    ],
)
predicate = "id = 3"
print("Selected:", table.search().where(predicate).to_list())
table.delete(predicate)
rows = sorted(table.search().to_list(), key=lambda row: row["id"])
print("Remaining:", rows)
```

```typescript TypeScript 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" },
    { id: 2, name: "Bob" },
    { id: 3, name: "Charlie" },
  ]);
  const predicate = "id = 3";
  console.log("Selected:", await table.query().where(predicate).toArray());
  await table.delete(predicate);
  const rows = (await table.query().toArray()).sort((a, b) => a.id - b.id);
  console.log("Remaining:", rows);
} finally {
  await rm(uri, { recursive: true, force: true });
}
```

```rust Rust 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)]) -> Box<dyn RecordBatchReader + Send> {
    let schema = Arc::new(Schema::new(vec![
        Field::new("id", DataType::Int64, false),
        Field::new("name", DataType::Utf8, 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))),
        ],
    )
    .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"), (2, "Bob"), (3, "Charlie")]),
        )
        .execute()
        .await?;
    let predicate = "id = 3";
    let selected: Vec<RecordBatch> = table
        .query()
        .only_if(predicate)
        .execute()
        .await?
        .try_collect()
        .await?;
    println!("Selected: {selected:?}");
    table.delete(predicate).await?;
    let rows: Vec<RecordBatch> = table.query().execute().await?.try_collect().await?;
    println!("Remaining: {rows:?}");
    assert_eq!(table.count_rows(None).await?, 2);
    assert_eq!(
        table
            .count_rows(Some("id = 1 AND name = 'Alice'".into()))
            .await?,
        1
    );
    assert_eq!(
        table
            .count_rows(Some("id = 2 AND name = 'Bob'".into()))
            .await?,
        1
    );
    assert_eq!(table.count_rows(Some("id = 3".into())).await?, 0);
    Ok(())
}
```

Enterprise also supports [`DELETE`](https://docs.lancedb.com/enterprise/v1-14-0/sql/dml/delete).

## Verify that the rows are absent

Query the same predicate after deletion and check the remaining row count.
A reader still using an older snapshot may see the earlier contents; see
[Consistency](https://docs.lancedb.com/enterprise/v1-14-0/tables/consistency.md).

Deleted rows are excluded from current query results, including indexed
searches. Deletion does not imply immediate physical erasure of every stored
copy of the data.

## Understand storage reclamation

Compaction rewrites the live data layout; cleanup removes eligible retained
versions and files. Retained history can continue to reference data that no
longer appears in the latest table version.

In LanceDB Embedded, `optimize()` provides maintenance operations. In Enterprise, managed
maintenance and retention follow the service configuration. Choose retention
according to the history you need, rather than shortening it as part of a
routine delete example. See [Compaction](https://docs.lancedb.com/enterprise/v1-14-0/maintenance/compaction.md) and
[Garbage Collection](https://docs.lancedb.com/enterprise/v1-14-0/maintenance/cleanup.md) for the separate maintenance workflow.

## 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://raw.githubusercontent.com/lancedb/lancedb/2af85bea8999c8cd23ec01ff62ba110c54652c10/docs/web/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.