SSC-EWI-TS0081

Using a full join in a delete statement is not supported

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

When transforming the DELETE statement, SnowConvert extracts the table references found in the FROM clause of the statement and moves them to the USING clause of the Snowflake delete statement.

In order to preserve the LEFT and RIGHT JOINs used in the original code, outer join syntax (+) is added to the conditions to indicate such behavior. However, the (+) syntax can not be used to indicate FULL JOINs in Snowflake, this EWI is used to warn the user about this limitation.

Example code

Input Code :

IN -> SqlServer_01.sql

DELETE Employees
FROM Employees FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentID IS NULL;

Output Code:

OUT -> SqlServer_01.sql

!!!RESOLVE EWI!!! /*** SSC-EWI-TS0081 - USING A FULL JOIN IN A DELETE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
DELETE FROM
Employees
USING Departments
WHERE
Departments.DepartmentID IS NULL
AND Employees.DepartmentID = Departments.DepartmentID;

Recommendations

• Check the logic of your FULL JOIN, it might be possible to rewrite it as other type of JOIN. For example, the code included in the example code is essentially the same as a LEFT JOIN:

Input:

IN -> SqlServer_02.sql

DELETE Employees
FROM Employees LEFT OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentID IS NULL;

Output:

OUT -> SqlServer_02.sql

DELETE FROM
    Employees
USING Departments
WHERE
    Departments.DepartmentID IS NULL
    AND Employees.DepartmentID = Departments.DepartmentID(+);

• If you need more support, you can email us at [email protected]