DELETE removes values stored in user-created tables.


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.


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.


CREATE TABLE delete_me (a int, b text);
INSERT INTO delete_me
    (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
Back to top ↑