# DELETE

The `DELETE` statement deletes rows from a table.

Warning:
If you delete a row that is referenced by a foreign key constraint and has an `ON DELETE` action, all of the dependent rows will also be deleted or updated.
Note:
To delete columns, see `DROP COLUMN`.

## Required privileges

The user must have the `DELETE` and `SELECT` privileges on the table.

## Synopsis

## Parameters

Parameter | Description
--- | ---
`common_table_expr` | See Common Table Expressions.
`table_name` | The name of the table that contains the rows you want to update.
`AS table_alias_name` | An alias for the table name. When an alias is provided, it completely hides the actual table name.
`WHERE a_expr` | `a_expr` must be an expression that returns Boolean values using columns (e.g., `<column> = <value>`). Delete rows that return `TRUE`. Without a `WHERE` clause in your statement, `DELETE` removes all rows from the table.
`sort_clause` | An `ORDER BY` clause. See Ordering Query Results for more details.
`limit_clause` | A `LIMIT` clause. See Limiting Query Results for more details.
`RETURNING target_list` | Return values based on rows deleted, where `target_list` can be specific column names from the table, `*` for all columns, or computations using scalar expressions. To return nothing in the response, not even the number of rows updated, use `RETURNING NOTHING`.

## Success responses

Successful `DELETE` statements return one of the following:

Response | Description
--- | ---
`DELETE` `int` | int rows were deleted. `DELETE` statements that do not delete any rows respond with `DELETE 0`. When `RETURNING NOTHING` is used, this information is not included in the response.
Retrieved table | Including the `RETURNING` clause retrieves the deleted rows, using the columns identified by the clause's parameters. See an example.

## Disk space usage after deletes

Deleting a row does not immediately free up the disk space. This is due to the fact that CockroachDB retains the ability to query tables historically.

If disk usage is a concern, the solution is to reduce the time-to-live (TTL) for the zone by setting `gc.ttlseconds` to a lower value, which will cause garbage collection to clean up deleted objects (rows, tables) more frequently.

## Select performance on deleted rows

Queries that scan across tables that have lots of deleted rows will have to scan over deletions that have not yet been garbage collected. Certain database usage patterns that frequently scan over and delete lots of rows will want to reduce the time-to-live values to clean up deleted rows more frequently.

## Delete performance on large data sets

If you are deleting a large amount of data using iterative `DELETE ... LIMIT` statements, you are likely to see a drop in performance for each subsequent `DELETE` statement.

For an explanation of why this happens, and for instructions showing how to iteratively delete rows in constant time, see Why are my deletes getting slower over time? from the FAQ.

## Examples

### Delete all rows

You can delete all rows from a table by not including a `WHERE` clause in your `DELETE` statement.

    > DELETE FROM account_details;

    DELETE 7

Tip:

Unless your table is small (less than 1000 rows), using `TRUNCATE` to delete the contents of a table will be more performant than using `DELETE`.

### Delete specific rows

When deleting specific rows from a table, the most important decision you make is which columns to use in your `WHERE` clause. When making that choice, consider the potential impact of using columns with the Primary Key/Unique constraints (both of which enforce uniqueness) versus those that are not unique.

#### Delete rows using Primary Key/unique columns

Using columns with the Primary Key or Unique constraints to delete rows ensures your statement is unambiguous—no two rows contain the same column value, so it's less likely to delete data unintentionally.

In this example, `account_id` is our primary key and we want to delete the row where it equals 1. Because we're positive no other rows have that value in the `account_id` column, there's no risk of accidentally removing another row.

    > DELETE FROM account_details WHERE account_id = 1 RETURNING *;

    +------------+---------+--------------+
    | account_id | balance | account_type |
    +------------+---------+--------------+
    |          1 |   32000 | Savings      |
    +------------+---------+--------------+

#### Delete rows using non-unique columns

Deleting rows using non-unique columns removes every row that returns `TRUE` for the `WHERE` clause's `a_expr`. This can easily result in deleting data you didn't intend to.

    > DELETE FROM account_details WHERE balance = 30000 RETURNING *;

    +------------+---------+--------------+
    | account_id | balance | account_type |
    +------------+---------+--------------+
    |          2 |   30000 | Checking     |
    |          3 |   30000 | Savings      |
    +------------+---------+--------------+

The example statement deleted two rows, which might be unexpected.

### Return deleted rows

To see which rows your statement deleted, include the `RETURNING` clause to retrieve them using the columns you specify.

#### Use all columns

By specifying `*`, you retrieve all columns of the delete rows.

    > DELETE FROM account_details WHERE balance < 23000 RETURNING *;

    +------------+---------+--------------+
    | account_id | balance | account_type |
    +------------+---------+--------------+
    |          4 |   22000 | Savings      |
    +------------+---------+--------------+

#### Use specific columns

To retrieve specific columns, name them in the `RETURNING` clause.

    > DELETE FROM account_details WHERE account_id = 5 RETURNING account_id, account_type;

    +------------+--------------+
    | account_id | account_type |
    +------------+--------------+
    |          5 | Checking     |
    +------------+--------------+

#### Change column labels

When `RETURNING` specific columns, you can change their labels using `AS`.

    > DELETE FROM account_details WHERE balance < 22500 RETURNING account_id, balance AS final_balance;

    +------------+---------------+
    | account_id | final_balance |
    +------------+---------------+
    |          6 |         23500 |
    +------------+---------------+

## See also

- `INSERT`
- `UPDATE`
- `UPSERT`
- `TRUNCATE`
- `ALTER TABLE`
- `DROP TABLE`
- `DROP DATABASE`
- Other SQL Statements
- Limiting Query Results