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 ] with_query [ , ... ] ] DELETE FROM
[ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ returning_clause ]
returning_clause ::= RETURNING { * | { output_expression
[ [ AS ] output_name ] }
[ , ... ] }
delete
returning_clause
Semantics
USING
clause is not yet supported.While the
WHERE
clause allows a wide range of operators, the exact conditions used in theWHERE
clause have significant performance considerations (especially for large datasets). For the best performance, use aWHERE
clause that provides values for all columns inPRIMARY KEY
orINDEX KEY
.
delete
WITH [ RECURSIVE ] with_query [ , … ] DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ 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 evaluate.
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)