# DELETE ... RETURNING

The `DELETE ... RETURNING` statement allows you to delete rows and return data from the deleted rows in a single statement. This avoids running a separate `SELECT` to see which rows were removed, reduces duplicate table scans, and simplifies application logic. The exact set of deleted rows is returned reliably, which is especially useful when the delete condition involves columns that are used in complex predicates.

In transactional workloads, `DELETE ... RETURNING` participates in transactions like any other DML statement. If the transaction is rolled back, the deletions are undone even though rows may have been returned to the client while the transaction was open.

**Syntax**:

```
DELETE FROM table_name
  [WHERE <where_condition>]
    [LIMIT row_count]
[RETURNING  * | column_name [AS alias] [, column_name [AS alias] ...]];
```

In the above syntax:

`RETURNING * ` - returns all columns of each deleted row.

`RETURNING <column_name> [, <column_name>; ...]` - returns only the listed columns from each deleted row.

`RETURNING <column_name> [AS] alias` - assigns an alias to a returned column. Aliases appear in the result set metadata and can be used by client applications.

For information on other arguments in the syntax refer [DELETE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/delete.md)

## Remarks

Supported on both rowstore and columnstore tables.

Supported in both single- and multi-host deployments.

Works for both rowstore and columnstore tables, but very large deletes may still be subject to the same memory and performance considerations as standard DELETE (for example, use `LIMIT` in batches when deleting many rows from narrow rowstore tables).

The `RETURNING` clause accepts only \* or column names (with optional aliases).

Expressions such as t.a+1, t.b\*2, t.b+t.c in the `RETURNING` list are not supported.

`DELETE ... RETURNING` produces a result set that clients consume the same way as a `SELECT` result:

* Each row in the result set corresponds to a deleted row.
* The columns of the result set are exactly those listed in the `RETURNING` clause, in the specified order.
* If you use \* all columns from the deleted table are returned.
* Column names in the result set reflect any aliases specified in the `RETURNING` clause.

The data visible in the `RETURNING` result set corresponds to the state of each row at the moment it is deleted.

For `DELETE`, all rows that appear in the result set are actually deleted from the table once the transaction commits.

Integration with Client Libraries - Because `DELETE ... RETURNING` uses the same protocol shape as `SELECT `(metadata + rows), client drivers and ORMs that support "DML with RETURNING" can consume the returned rows as a standard result set.

Any `DELETE` that involves more than one table (joins, multi-table deletes, or subselects in the target for returning multiple tables) is not supported with` RETURNING`.

Not supported in stored procedures or CTEs.

## Examples

**Example 1**: The following example deletes expired products and returns all deleted rows for logging or auditing. The result set contains one row per deleted product, with all columns from the table PRODUCTS.

```sql
DELETE FROM products
  WHERE expiration_date <= CURDATE()
RETURNING *;
```

**Example 2**: The following example deletes a limited number of events. The `LIMIT` applies both to the number of rows deleted and the rows returned (similar to `UPDATE ... RETURNING` with `LIMIT`). The result set includes at most 100 rows, each with ID and CREATED\_AT column values.

```sql
DELETE FROM events
  WHERE created_at < '2026-01-01'
    LIMIT 100
RETURNING id, created_at;
```

**Example 3**: The following example deletes specific employees and return selected columns.

```sql
DELETE FROM employees
  WHERE name IN ('Diana', 'Frank')
RETURNING id AS employee_id, name, salary;
```

**Example 4**: The following example deletes all employees in the 'Research' team and returns only the columns needed by the application, with aliases that match the client-side model.

```sql
DELETE FROM employees
  WHERE team = 'Research'
RETURNING name AS employee_name, salary, manager_id AS manager;
```

**Example 5**: The following example shows the traditional pattern vs. `DELETE ... RETURNING`. Without `RETURNING,` an application that needs to determine which rows were deleted typically runs two statements:

```sql
BEGIN;

SELECT *FROM products
  WHERE expiration_date <= CURDATE()FOR UPDATE;

DELETE FROM products
  WHERE expiration_date <= CURDATE();

COMMIT;
```

With `DELETE ... RETURNING`, the same workflow becomes a single statement, thereby avoids scanning the table twice and reduces the risk of anomalies related to re-identifying rows (e.g. phantom reads), since the delete and data retrieval happen atomically in a single statement.

```sql
DELETE FROM products
  WHERE expiration_date <= CURDATE()
RETURNING *;
```

***

Modified at: February 17, 2026

Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/delete-returning/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/delete-returning/)

(An index of the documentation is available at /llms.txt)