DELETE

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

delete ::= [ WITH [ RECURSIVE ] { common_table_expression [ , ... ] } ] 
            DELETE FROM table_expr [ [ AS ] alias ]  
           [ WHERE boolean_expression | WHERE CURRENT OF cursor_name ] 
            [ returning_clause ]

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

delete

WITHRECURSIVE,common_table_expressionDELETEFROMtable_exprASaliasWHEREboolean_expressionWHERECURRENTOFcursor_namereturning_clause

returning_clause

RETURNING*,output_expressionASoutput_name

Table inheritance is not yet supported

The table_expr rule specifies syntax that is useful only when at least one other table inherits one of the tables that the truncate statement lists explicitly. See this note for more detail. Until inheritance is supported, use a bare table_name.

See the section The WITH clause and common table expressions for more information about the semantics of the common_table_expression grammar rule.

Semantics

  • USING clause is not yet supported.

  • 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 ] [ 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.

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.

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

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

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)

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

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