'DELETE FROM + LEFT JOIN SNOWFLAKE

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 ?

sqlsnowflake-cloud-data-platform


Solution 1:[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'

Solution 2:[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');

Solution 3:[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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 eshirvana
Solution 2
Solution 3 j1yuan

Related Questions