我的书签
添加书签
移除书签DELETE
The DELETE statement deletes rows from a table.
Warning:
If you delete a row that is referenced by a foreign key constraint and has an ON DELETE action, all of the dependent rows will also be deleted or updated.
Note:
To delete columns, see DROP COLUMN.
Required privileges
The user must have the DELETE and SELECT privileges on the table.
Synopsis
Parameters
| Parameter | Description |
|---|---|
common_table_expr | See Common Table Expressions. |
table_name | The name of the table that contains the rows you want to update. |
AS table_alias_name | An alias for the table name. When an alias is provided, it completely hides the actual table name. |
WHERE a_expr | a_expr must be an expression that returns Boolean values using columns (e.g., <column> = <value>). Delete rows that return TRUE.Without a WHERE clause in your statement, DELETE removes all rows from the table. |
sort_clause | An ORDER BY clause. New in v19.1: The ORDER BY clause can no longer be used with a DELETE statement when there is no LIMIT clause present. |
limit_clause | A LIMIT clause. See Limiting Query Results for more details. |
RETURNING target_list | Return values based on rows deleted, where target_list can be specific column names from the table, * for all columns, or computations using scalar expressions. To return nothing in the response, not even the number of rows updated, use RETURNING NOTHING. |
Success responses
Successful DELETE statements return one of the following:
| Response | Description |
|---|---|
DELETE int | int rows were deleted.DELETE statements that do not delete any rows respond with DELETE 0. When RETURNING NOTHING is used, this information is not included in the response. |
| Retrieved table | Including the RETURNING clause retrieves the deleted rows, using the columns identified by the clause's parameters.See an example. |
Disk space usage after deletes
Deleting a row does not immediately free up the disk space. This isdue to the fact that CockroachDB retains the ability to query tableshistorically.
If disk usage is a concern, the solution is toreduce the time-to-live (TTL) forthe zone by setting gc.ttlseconds to a lower value, which will causegarbage collection to clean up deleted objects (rows, tables) morefrequently.
Select performance on deleted rows
Queries that scan across tables that have lots of deleted rows willhave to scan over deletions that have not yet been garbagecollected. Certain database usage patterns that frequently scan overand delete lots of rows will want to reduce thetime-to-live values to clean updeleted rows more frequently.
Sorting the output of deletes
To sort the output of a DELETE statement, use:
> SELECT ... FROM [DELETE ...] ORDER BY ...
For an example, see Sort and return deleted rows.
For more information about ordering query results in general, seeOrdering Query Results.
Examples
Delete all rows
You can delete all rows from a table by not including a WHERE clause in your DELETE statement.
> DELETE FROM account_details;
DELETE 7
Tip:
Unless your table is small (less than 1000 rows), using TRUNCATE to delete the contents of a table will be more performant than using DELETE.
Delete specific rows
When deleting specific rows from a table, the most important decision you make is which columns to use in your WHERE clause. When making that choice, consider the potential impact of using columns with the Primary Key/Unique constraints (both of which enforce uniqueness) versus those that are not unique.
Delete rows using Primary Key/unique columns
Using columns with the Primary Key or Unique constraints to delete rows ensures your statement is unambiguous—no two rows contain the same column value, so it's less likely to delete data unintentionally.
In this example, account_id is our primary key and we want to delete the row where it equals 1. Because we're positive no other rows have that value in the account_id column, there's no risk of accidentally removing another row.
> DELETE FROM account_details WHERE account_id = 1 RETURNING *;
account_id | balance | account_type------------+---------+--------------1 | 32000 | Savings(1 row)DELETE 1
Delete rows using non-unique columns
Deleting rows using non-unique columns removes every row that returns TRUE for the WHERE clause's a_expr. This can easily result in deleting data you didn't intend to.
> DELETE FROM account_details WHERE balance = 30000 RETURNING *;
account_id | balance | account_type------------+---------+--------------2 | 30000 | Checking3 | 30000 | Savings(2 rows)DELETE 2
The example statement deleted two rows, which might be unexpected.
Return deleted rows
To see which rows your statement deleted, include the RETURNING clause to retrieve them using the columns you specify.
Use all columns
By specifying *, you retrieve all columns of the delete rows.
> DELETE FROM account_details WHERE balance < 23000 RETURNING *;
account_id | balance | account_type------------+---------+--------------4 | 22000 | Savings(1 row)DELETE 1
Use specific columns
To retrieve specific columns, name them in the RETURNING clause.
> DELETE FROM account_details WHERE account_id = 5 RETURNING account_id, account_type;
account_id | account_type------------+--------------5 | Checking(1 row)DELETE 1
Change column labels
When RETURNING specific columns, you can change their labels using AS.
> DELETE FROM account_details WHERE balance < 24500 RETURNING account_id, balance AS final_balance;
account_id | final_balance------------+---------------6 | 23500(1 row)DELETE 1
Sort and return deleted rows
To sort and return deleted rows, use a statement like the following:
> SELECT * FROM [DELETE FROM account_details RETURNING *] ORDER BY account_id;
account_id | balance | account_type------------+----------+--------------7 | 79493.51 | Checking8 | 40761.66 | Savings9 | 2111.67 | Checking10 | 59173.15 | Savings(4 rows)
