---
title: "DELETE statement"
description: "The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself."
canonical: "https://motherduck.com/glossary/delete-statement/"
related:
  - title: "DELETE | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/delete/"
  - title: "DuckLake Architecture Deep Dive"
    url: "https://motherduck.com/blog/ducklake-architecture-deep-dive/"
  - title: "DuckDB statements | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/"
gated_asset:
  title: "DuckLake: The Lakehouse Table Format"
  url: "https://motherduck.com/lp/ducklake-lakehouse-table-format-book-full/"
---

# DELETE statement

> The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.

## Overview
`DELETE` is the DML statement for removing rows from a table. Its basic form:

```sql
DELETE FROM orders WHERE status = 'cancelled';
```

Omitting `WHERE` deletes every row in the table while leaving the table's structure (columns, constraints, indexes) intact — unlike `DROP TABLE`, which removes the table entirely, or `TRUNCATE`, which is a faster bulk-delete of all rows.

<glossary-callout guide="duckdb-cheatsheet-full" />

## Deleting based on another table
Some engines allow a `USING` clause to delete rows based on a join against another table or subquery, which is more efficient and readable than a correlated subquery in the `WHERE` clause:

```sql
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.is_test_account = true;
```

## DuckDB specifics
DuckDB supports `DELETE FROM table [USING other_tables] WHERE condition`, letting you delete rows based on the content of other tables or subqueries. DuckDB also supports a `RETURNING` clause that returns the deleted rows before they're removed — handy for auditing or archiving what was deleted in the same statement:

```sql
DELETE FROM employees
WHERE termination_date < CURRENT_DATE - INTERVAL '7 years'
RETURNING employee_id, name;
```

A practical DuckDB detail: `DELETE` marks rows as deleted rather than immediately reclaiming their disk space; space is fully reclaimed on the next `CHECKPOINT`. For clearing an entire table quickly without a `WHERE` clause, `TRUNCATE` is functionally equivalent to an unconditional `DELETE FROM` in DuckDB.