Materialize/doc/user/content/sql/delete.md

---

title: "DELETE" description: "DELETE removes values stored in tables." menu: main:

parent: 'commands'

---

DELETE removes values stored in user-created tables.

Syntax

{{< diagram "delete-stmt.svg" >}}

Field	Use
DELETE FROM _tablename	The table whose values you want to remove.
alias	Only permit references to _tablename as alias.
USING _fromitem	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	Only remove rows which evaluate to true for condition.

Details

Known limitations

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

Examples

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
---+---

Privileges

The privileges required to execute this statement are:

{{< include-md file="shared-content/sql-command-privileges/delete.md" >}}

Related pages

• CREATE TABLE
• INSERT
• SELECT