SSC-EWI-TS0081

Using a full join in a delete statement is not supported

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 ***/!!!
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 [e-mail]