Materialize Documentation
s
Join the Community github/materialize
Home  /  Reference

DELETE

DELETE removes values stored in user-created tables.

Syntax

DELETE FROM table_name AS alias USING from_item , WHERE condition
Field Use
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 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

DELETE cannot currently be used inside transactions.

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