How to DELETE with JOIN in Snowflake

Sometimes the rows you want to delete are defined by another table. In Snowflake, USING makes that table available to the deletion condition.

A small example

Suppose Students contains Luke Green, Mary Brown and John Red. A second table identifies the student records to remove.

CREATE TEMP TABLE Students (Name VARCHAR, Surname VARCHAR);
INSERT INTO Students VALUES
  ('Luke', 'Green'), ('Mary', 'Brown'), ('John', 'Red');

CREATE TEMP TABLE Rejected (Name VARCHAR, Surname VARCHAR);
INSERT INTO Rejected VALUES ('John', 'Red');

Preview the match

SELECT s.*
FROM Students s
JOIN Rejected r
  ON s.Name = r.Name AND s.Surname = r.Surname;

The expected match is John Red. For real data, use stable identifiers instead of names.

Delete matching rows

DELETE FROM Students
USING Rejected
WHERE Students.Name = Rejected.Name
  AND Students.Surname = Rejected.Surname;

Luke and Mary remain in this example. Both parts of the matching condition matter: joining on a first name alone could remove unrelated people.

Check the scope

Review the matching rows and keep a recovery path before applying this to production data. Without a WHERE condition, a delete can affect the whole table. When multiple joined rows match a target row, one qualifying match is enough for deletion.

References

Snowflake DELETE reference