DELETE FROM
Warning
Currently, mixing column-oriented tables and row-oriented tables in a single transaction is supported only if the transaction performs read operations; no writes are allowed. Support for read-write transactions involving both table types is under development.
If a write transaction includes both types of tables, it fails with the following error: Write transactions between column and row tables are disabled at current time.
Deletes rows that match the WHERE clause, from the table.
Example
DELETE FROM my_table
WHERE Key1 == 1 AND Key2 >= "One";
DELETE FROM ... ON
Deletes rows based on the results of a subquery. The set of columns returned by the subquery must be a subset of the table's columns being updated, and all columns of the table's primary key must be present in the returned columns. The data types of the columns returned by the subquery must match the data types of the corresponding columns in the table.
The primary key value is used to search for rows to be deleted from the table. The presence of other (non-key) columns of the table in the output of the subquery does not affect the results of the deletion operation.
Example
$to_delete = (
SELECT Key, SubKey FROM my_table WHERE Value = "ToDelete" LIMIT 100
);
DELETE FROM my_table ON
SELECT * FROM $to_delete;
DELETE FROM ... RETURNING
Deletes rows and returns their values in a single operation. It allows to retrieve data from the rows being deleted without needing to perform a separate SELECT query beforehand.
Examples
- Return all values of modified rows
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING *;
- Return specific columns
DELETE FROM orders
WHERE status = 'cancelled'
RETURNING order_id, order_date;