DELETE FROM LEFT JOIN SNOWFLAKE-CodePudding

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 ?

CodePudding user response：

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'

CodePudding user response：

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');