DELETE FROM + LEFT JOIN SNOWFLAKE

Question

I'm trying to delete rows from table using delete.

delete a
from "table1" as a
LEFT JOIN "table2" AS b 
on a."x" = b."x" 
where b."X" = 'X'

but everything I get is

SQL compilation error: syntax error line 1 at position 7 unexpected 'a'. syntax error line 3 at position 0 unexpected 'LEFT'.

Can you please help me ?

Answer 1

here is the right syntax in snowflake:

delete from "table1" as a
USING "table2" as b 
WHERE a."x" = b."x" 
  and b."X" = 'X'

Answer 2

Using WHERE and refering to specific value from outer table effectively makes a LEFT JOIN an INNER JOIN:

SELECT * -- DELETE a
FROM "table1" as a
LEFT JOIN "table2" AS b 
ON a."x" = b."x" 
WHERE b."X" = 'X';

---

behaves the same as:

SELECT *  -- DELETE a
FROM "table1" as a
INNER JOIN "table2" AS b 
ON a."x" = b."x" 
WHERE b."X" = 'X';

---

Depending of requirements it could be rewritten with EXISTS/NOT EXISTS:

DELETE FROM "table1"
WHERE NOT EXISTS (SELECT 1 
                 FROM "table2" 
                 WHERE "table1"."x" = "table2"."x"
                 AND "table2"."x" = 'X');

Answer 3

Here is another alternative - using a "merge" query:

MERGE INTO "table1" a
USING
(
    SELECT x
    FROM "table2"
    where x = 'X'
) b
ON a.x = b.x
WHEN MATCHED THEN delete