> ## Documentation Index
> Fetch the complete documentation index at: https://www.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Delete Data

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

This page has instructions for deleting rows of data from CockroachDB, using the <InternalLink path="delete">`DELETE`</InternalLink> <InternalLink path="sql-statements">SQL statement</InternalLink>.

## Before you begin

Before reading this page, do the following:

* <InternalLink version="cockroachcloud" path="quickstart">Create a CockroachDB Standard cluster</InternalLink> or <InternalLink version="cockroachcloud" path="quickstart?filters=local">start a local cluster</InternalLink>.
* <InternalLink path="install-client-drivers">Install a Driver or ORM Framework</InternalLink>.
* <InternalLink path="connect-to-the-database">Connect to the database</InternalLink>.
* <InternalLink path="schema-design-overview">Create a database schema</InternalLink>.
* <InternalLink path="insert-data">Insert data</InternalLink> that you now want to delete.

  In the examples on this page, we use sample <InternalLink path="movr">`movr`</InternalLink> data imported with the <InternalLink path="cockroach-workload">`cockroach workload` command</InternalLink>.

## Use `DELETE`

To delete rows in a table, use a <InternalLink path="update">`DELETE` statement</InternalLink> with a `WHERE` clause that filters on the columns that identify the rows that you want to delete.

### SQL syntax

In SQL, `DELETE` statements generally take the following form:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DELETE FROM {table} WHERE {filter_column} {comparison_operator} {filter_value}
```

Where:

* <code>{'{table}'}</code> is a table with rows that you want to delete.
* <code>{'{filter_column}'}</code> is the column to filter on.
* <code>{'{comparison_operator}'}</code> is a <InternalLink path="functions-and-operators#operators">comparison operator</InternalLink> that resolves to `TRUE` or `FALSE` (e.g., `=` ).
* <code>{'{filter_value}'}</code> is the matching value for the filter.

<Tip>
  For detailed reference documentation on the `DELETE` statement, including additional examples, see the <InternalLink path="delete">`DELETE` syntax page</InternalLink>.
</Tip>

### Best practices

Here are some best practices to follow when deleting rows:

* Limit the number of `DELETE` statements that you execute. It's more efficient to delete multiple rows with a single statement than to execute multiple `DELETE` statements that each delete a single row.
* Always specify a `WHERE` clause in `DELETE` queries. If no `WHERE` clause is specified, CockroachDB will delete all of the rows in the specified table.
* To delete all of the rows in a table, use a <InternalLink path="truncate">`TRUNCATE` statement</InternalLink> instead of a `DELETE` statement.
* To delete a large number of rows (i.e., tens of thousands of rows or more), use a <InternalLink path="bulk-delete-data#batch-delete-on-an-indexed-column">batch-delete loop</InternalLink>.
* When executing `DELETE` statements from an application, make sure that you wrap the SQL-executing functions in <InternalLink path="query-behavior-troubleshooting#transaction-retry-errors">a retry loop that handles transaction errors</InternalLink> that can occur under <InternalLink path="performance-best-practices-overview#transaction-contention">contention</InternalLink>.
* Review the [performance considerations below](#performance-considerations).

### Examples

#### Delete rows filtered on a non-unique column

Suppose that you want to delete the vehicle location history data recorded during a specific hour of a specific day. To delete all of the rows in the `vehicle_location_histories` table where the `timestamp` is between two <InternalLink path="timestamp">`TIMESTAMP`</InternalLink> values:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN '2021-03-17 14:00:00' AND '2021-03-17 15:00:00';
```

For more information about how to use the built-in SQL client, see the <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink> reference docs.

```go theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// 'db' is an open database connection

tsOne := "2021-03-17 14:00:00"
tsTwo := "2021-03-17 15:00:00"

if _, err := db.Exec("DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN $1 AND $2", tsOne, tsTwo); err != nil {
  return err
}
return nil
```

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// ds is an org.postgresql.ds.PGSimpleDataSource

String tsOne = "2021-03-17 14:00:00";
String tsTwo = "2021-03-17 15:00:00";

try (Connection connection = ds.getConnection()) {
    PreparedStatement p = connection.prepareStatement("DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN ? AND ?");
    p.setString(1, tsOne);
    p.setString(2, tsTwo);
    p.executeUpdate();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
            e.getMessage());
}
```

```python theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
# conn is a psycopg2 connection

tsOne = '2021-03-17 14:00:00'
tsTwo = '2021-03-17 15:00:00'

with conn.cursor() as cur:
    cur.execute(
        "DELETE FROM vehicle_location_histories WHERE timestamp BETWEEN %s AND %s", (tsOne, tsTwo))
```

<Tip>
  If the `WHERE` clause evaluates to `TRUE` for a large number of rows (i.e., tens of thousands of rows), use a <InternalLink path="bulk-delete-data#batch-delete-on-an-indexed-column">batch-delete loop</InternalLink> instead of executing a simple `DELETE` query.
</Tip>

#### Delete rows filtered on a unique column

Suppose that you want to delete the promo code data for a specific set of codes. To delete the rows in the `promo_codes` table where the `code` matches a string in a set of string values:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DELETE from promo_codes WHERE code IN ('0_explain_theory_something', '100_address_garden_certain', '1000_do_write_words');
```

For more information about how to use the built-in SQL client, see the <InternalLink path="cockroach-sql">`cockroach sql`</InternalLink> reference docs.

```go theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// 'db' is an open database connection

codeOne := "0_explain_theory_something"
codeTwo := "100_address_garden_certain"
codeThree := "1000_do_write_words"

if _, err := db.Exec("DELETE from promo_codes WHERE code IN ($1, $2, $3)", codeOne, codeTwo, codeThree); err != nil {
  return err
}
return nil
```

```java theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
// ds is an org.postgresql.ds.PGSimpleDataSource

String codeOne = "0_explain_theory_something";
String codeTwo = "100_address_garden_certain";
String codeThree = "1000_do_write_words";

try (Connection connection = ds.getConnection()) {
    PreparedStatement p = connection.prepareStatement("DELETE from promo_codes WHERE code IN(?, ?, ?)");
    p.setString(1, codeOne);
    p.setString(2, codeTwo);
    p.setString(3, codeThree);
    p.executeUpdate();

} catch (SQLException e) {
    System.out.printf("sql state = [%s]\ncause = [%s]\nmessage = [%s]\n", e.getSQLState(), e.getCause(),
            e.getMessage());
}
```

```python theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
# conn is a psycopg2 connection

codeOne = '0_explain_theory_something'
codeTwo = '100_address_garden_certain'
codeThree = '1000_do_write_words'

with conn.cursor() as cur:
    cur.execute("DELETE from promo_codes WHERE code IN (%s, %s, %s)", (codeOne, codeTwo, codeThree)),
```

## Performance considerations

Because of the way CockroachDB works under the hood, deleting data from the database does not immediately reduce disk usage. Instead, records are marked as "deleted" and processed asynchronously by a background garbage collection process. Once the marked records are older than <InternalLink path="configure-replication-zones#gc-ttlseconds">the specified TTL interval</InternalLink>, they are eligible to be removed. The garbage collection interval is designed to allow sufficient time for running <InternalLink path="take-full-and-incremental-backups">backups</InternalLink> and <InternalLink path="as-of-system-time">time travel queries using `AS OF SYSTEM TIME`</InternalLink>. The garbage collection interval is controlled by the <InternalLink path="configure-replication-zones#gc-ttlseconds">`gc.ttlseconds`</InternalLink> setting.

The practical implications of the above are:

* Deleting data will not immediately decrease disk usage.
* If you issue multiple <InternalLink path="delete">`DELETE`</InternalLink> statements in sequence that each delete large amounts of data, each subsequent `DELETE` statement will run more slowly. For details, see <InternalLink path="delete#preserving-delete-performance-over-time">Preserving `DELETE` performance over time</InternalLink>.

For more information about how the storage layer of CockroachDB works, see the <InternalLink path="architecture/storage-layer">storage layer reference documentation</InternalLink>.

## See also

Reference information related to this task:

* <InternalLink path="delete">`DELETE`</InternalLink>
* <InternalLink path="bulk-delete-data">Bulk-delete data</InternalLink>
* <InternalLink path="row-level-ttl">Batch Delete Expired Data with Row-Level TTL</InternalLink>
* <InternalLink path="delete#disk-space-usage-after-deletes">Disk space usage after deletes</InternalLink>
* <InternalLink path="truncate">`TRUNCATE`</InternalLink>
* <InternalLink path="drop-table">`DROP TABLE`</InternalLink>
* <InternalLink path="performance-best-practices-overview#transaction-contention">Transaction Contention</InternalLink>

Other common tasks:

* <InternalLink path="connect-to-the-database">Connect to the Database</InternalLink>
* <InternalLink path="insert-data">Insert Data</InternalLink>
* <InternalLink path="query-data">Query Data</InternalLink>
* <InternalLink path="update-data">Update Data</InternalLink>
* <InternalLink path="run-multi-statement-transactions">Run Multi-Statement Transactions</InternalLink>
* <InternalLink path="query-behavior-troubleshooting">Troubleshoot SQL Statements</InternalLink>
* <InternalLink path="make-queries-fast">Optimize Statement Performance</InternalLink>
* <InternalLink path="example-apps">Example Apps</InternalLink>