ContextQMD
Back to Materialize

DELETE

doc/user/content/sql/delete.md

1231.9 KB
Original Source

DELETE removes values stored in user-created tables.

Syntax

mzsql
DELETE FROM <table_name> [AS <alias>]
[USING <from_item> [, ...]]
[WHERE <condition>]
;
Syntax elementDescription
<table_name>The table whose values you want to remove.
AS <alias>Optional. The alias for the table. If specified, only permit references to <table_name> as <alias>.
USING from_itemOptional. Table expressions whose columns you want to reference in the WHERE clause. This supports the same syntax as the FROM clause in SELECT statements, e.g. supporting aliases.
WHERE <condition>Optional. Only remove rows which evaluate to true for condition.

Details

Known limitations

  • DELETE cannot be used inside transactions.
  • DELETE can reference read-write tables but not sources or read-only tables.
  • Low performance. While processing a DELETE statement, Materialize cannot process other INSERT, UPDATE, or DELETE statements.

Examples

mzsql
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
mzsql
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
mzsql
DELETE FROM delete_me;
SELECT * FROM delete_me;

 a | b
---+---

Privileges

The privileges required to execute this statement are:

{{% include-headless "/headless/sql-command-privileges/delete" %}}