---
title: "DELETE"
url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/dml_delete/"
---

# DELETE

Use the DELETE statement to remove rows that meet certain conditions, and when conditions are not provided in WHERE clause, all rows are deleted.

## Synopsis

Use the `DELETE` statement to remove rows that meet certain conditions, and when conditions are not provided in WHERE clause, all rows are deleted. `DELETE` outputs the number of rows that are being deleted.

## Syntax

- [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-2)
- [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-2)

```output.ebnf
delete ::= [ with_clause ]  DELETE FROM table_expr [ [ AS ] alias ]  
           [ USING from_item ]  [ WHERE boolean_expression
                                  | WHERE CURRENT OF cursor_name ]  
           [ returning_clause ]

returning_clause ::= RETURNING { * | { output_expression 
                                     [ [ AS ] output_name ] } 
                                     [ , ... ] }
```

#### delete

[with\_clause](/stable/api/ysql/syntax_resources/grammar_diagrams#with-clause)DELETEFROM[table\_expr](/stable/api/ysql/syntax_resources/grammar_diagrams#table-expr)AS[alias](/stable/api/ysql/syntax_resources/grammar_diagrams#alias)USING[from\_item](/stable/api/ysql/syntax_resources/grammar_diagrams#from-item)WHERE[boolean\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#boolean-expression)WHERECURRENTOF[cursor\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#cursor-name)[returning\_clause](#returning-clause)

#### returning\_clause

RETURNING\*,[output\_expression](/stable/api/ysql/syntax_resources/grammar_diagrams#output-expression)AS[output\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#output-name)

Table inheritance

By default, `DELETE` on a table also updates matching rows in tables that inherit from it. Use `ONLY` before the table name to update the named table alone; `*` after it makes the default explicit. See [Table inheritance](/stable/api/ysql/the-sql-language/ddl-inherit/ "Table inheritance").

See the section [The WITH clause and common table expressions](/stable/api/ysql/the-sql-language/with-clause/ "The WITH clause and common table expressions") for more information about the semantics of the `common_table_expression` grammar rule.

## Semantics

- While the `WHERE` clause allows a wide range of operators, the exact conditions used in the `WHERE` clause have significant performance considerations (especially for large datasets). For the best performance, use a `WHERE` clause that provides values for all columns in `PRIMARY KEY` or `INDEX KEY`.

### *delete*

#### WITH \[ RECURSIVE ] *with\_query* \[ , ... ] DELETE FROM \[ ONLY ] *table\_name* \[ * ] \[ \[ AS ] *alias* ] \[USING from\_item] \[ WHERE *condition* | WHERE CURRENT OF *cursor\_name* ] \[ \[*returning\_clause*] (#returning-clause) ]

##### *with\_query*

Specify the subqueries that are referenced by name in the DELETE statement.

##### *table\_name*

Specify the name of the table to be deleted.

##### *alias*

Specify the identifier of the target table within the DELETE statement. When an alias is specified, it must be used in place of the actual table in the statement.

##### *from\_item*

A table expression that enables referencing columns from additional tables within the `WHERE` clause. This follows the identical syntax conventions as the `FROM` clause in a `SELECT` statement, including the ability to define table aliases. The target table should not be included as a `from_item` unless performing a self-join operation, in which case the target table must appear with an alias in the `from_item` list.

### *returning\_clause*

#### RETURNING

Specify the value to be returned. When the *output\_expression* references a column, the existing value of this column (deleted value) is used to returned.

#### *output\_name*

## Examples

Create a sample table, insert a few rows, then delete one of the inserted row.

```plpgsql
CREATE TABLE sample(k1 int, k2 int, v1 int, v2 text, PRIMARY KEY (k1, k2));
```

```plpgsql
INSERT INTO sample VALUES (1, 2.0, 3, 'a'), (2, 3.0, 4, 'b'), (3, 4.0, 5, 'c');
```

```plpgsql
yugabyte=# SELECT * FROM sample ORDER BY k1;
```

```
 k1 | k2 | v1 | v2
----+----+----+----
  1 |  2 |  3 | a
  2 |  3 |  4 | b
  3 |  4 |  5 | c
(3 rows)
```

```plpgsql
DELETE FROM sample WHERE k1 = 2 AND k2 = 3;
```

```plpgsql
yugabyte=# SELECT * FROM sample ORDER BY k1;
```

```
DELETE 1
```

```
 k1 | k2 | v1 | v2
----+----+----+----
  1 |  2 |  3 | a
  3 |  4 |  5 | c
(2 rows)
```

## See also

- [INSERT](/stable/api/ysql/the-sql-language/statements/dml_insert "INSERT")
- [SELECT](/stable/api/ysql/the-sql-language/statements/dml_select/ "SELECT")
- [UPDATE](/stable/api/ysql/the-sql-language/statements/dml_update/ "UPDATE")