DELETE removes values stored in user-created tables.
|DELETE FROM table_name||The table whose values you want to remove.|
|alias||Only permit references to table_name as alias.|
|USING from_item||Table expressions whose columns you want to reference in the
|WHERE condition||Only remove rows which evaluate to
DELETEcannot be used inside transactions.
DELETEcan reference user-created tables but not sources.
- Low performance. While processing a
DELETEstatement, Materialize cannot process other
CREATE TABLE delete_me (a int, b text); INSERT INTO delete_me VALUES (1, 'hello'), (2, 'goodbye'), (3, 'ok'); DELETE FROM delete_me WHERE b = 'hello'; SELECT * FROM delete_me ORDER BY a;
a | b ---+--------- 2 | goodbye 3 | ok
CREATE TABLE delete_using (b text); INSERT INTO delete_using VALUES ('goodbye'), ('ciao'); DELETE FROM delete_me USING delete_using WHERE delete_me.b = delete_using.b; SELECT * FROM delete_me;
a | b ---+---- 3 | ok
DELETE FROM delete_me; SELECT * FROM delete_me;
a | b ---+---
The privileges required to execute this statement are:
USAGEprivileges on the schemas that all relations and types in the query are contained in.
SELECTprivileges on all relations in the query.
- NOTE: if any item is a view, then the view owner must also have the necessary privileges to execute the view definition.
USAGEprivileges on all types used in the query.
USAGEprivileges on the active cluster.