In cases where the UPDATE statement has a FROM clause that contains JOINs, however, there are some changes done, since Snowflake does not allow the usage of JOINs with the table being updated.
These changes are as follows:
All the joined elements (either tables or subqueries) are moved to the list of FROM elements
All the expressions that come inside the ON clauses after each JOIN are moved to a WHERE clause, joined by AND. If a WHERE clause already exists in the statement, the original expressions of the WHERE clause are preserved at the end of that WHERE clause, while the expressions moved from the ON clause is added at the beginning.
Depending on if the JOIN was LEFT JOIN or RIGHT JOIN, the expression to the left or right of the conditional operation will include as a way to replicate the functionality of the original JOIN.
LEFT JOIN: The right side of the conditional is modified.
RIGHT JOIN: The left side of the conditional is modified
SQL Server
IN -> SqlServer_02.sql
UPDATE
test_sales.commissions
SET
test_sales.commissions.commission = 5
FROM
test_sales.commissions c
LEFT JOIN
test_sales.targets t
ON
c.target_id = t.target_id
RIGHT JOIN
(SELECT * FROM ATABLE) a
ON
c.target_id = a.target_id;
Snowflake
OUT -> SqlServer_02.sql
UPDATE test_sales.commissions c
SET
test_sales.commissions.commission = 5
FROM
test_sales.targets t,
(SELECT
*
FROM
ATABLE
) a
WHERE
test_sales.commissions.target_id = t.target_id(+)
AND test_sales.commissions.target_id(+) = a.target_id;
Cartesian Products
SQL Server allows add circular references between the target table of the Update Statement and the FROM Clause/ In execution time, the database optimizer removes any cartesian product generated. Otherwise, Snowflake currently does not optimize this scenario, producing a cartesian product that can be checked in the Execution Plan.
To resolve this, if there is a JOIN where one of their tables is the same as the update target, this reference is removed and added to the WHERE clause, and it is used to just filter the data and avoid making a set operation.
SQL Server
IN -> SqlServer_03.sql
UPDATE [HumanResources].[EMPLOYEEDEPARTMENTHISTORY_COPY]
SET
BusinessEntityID = b.BusinessEntityID ,
DepartmentID = b.DepartmentID,
ShiftID = b.ShiftID,
StartDate = b.StartDate,
EndDate = b.EndDate,
ModifiedDate = b.ModifiedDate
FROM [HumanResources].[EMPLOYEEDEPARTMENTHISTORY_COPY] AS a
RIGHT OUTER JOIN [HumanResources].[EmployeeDepartmentHistory] AS b
ON a.BusinessEntityID = b.BusinessEntityID and a.ShiftID = b.ShiftID;
Snowflake
OUT -> SqlServer_03.sql
UPDATE HumanResources.EMPLOYEEDEPARTMENTHISTORY_COPY a
SET
BusinessEntityID = b.BusinessEntityID,
DepartmentID = b.DepartmentID,
ShiftID = b.ShiftID,
StartDate = b.StartDate,
EndDate = b.EndDate,
ModifiedDate = b.ModifiedDate
FROM
HumanResources.EmployeeDepartmentHistory AS b
WHERE
HumanResources.EMPLOYEEDEPARTMENTHISTORY_COPY.BusinessEntityID = b.BusinessEntityID(+)
AND HumanResources.EMPLOYEEDEPARTMENTHISTORY_COPY.ShiftID = b.ShiftID;
Known Issues
OUTPUT clause
The OUTPUT clause is not supported by Snowflake.
SQL Server
IN -> SqlServer_04.sql
Update UpdateTest2
Set Col1 = 5
OUTPUT
deleted.Col1,
inserted.Col1
into ValuesTest;
Snowflake
OUT -> SqlServer_04.sql
Update UpdateTest2
Set
Col1 = 5
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - OUTPUT CLAUSE NOT SUPPORTED ***/!!!
OUTPUT
deleted.Col1,
inserted.Col1
into ValuesTest;
CTE
The WITH CTE clause is moved to the internal query in the update statement to be supported by Snowflake.
SQL Server
IN -> SqlServer_05.sql
With ut as (select * from UpdateTest3)
Update x
Set Col1 = 5
from ut as x;
Snowflake
OUT -> SqlServer_05.sql
UPDATE UpdateTest3
Set
Col1 = 5
FROM
(
WITH ut as (select
*
from
UpdateTest3
)
SELECT
*
FROM
ut
) AS x;
TOP clause
The TOP clause is not supported by Snowflake.
SQL Server
IN -> SqlServer_06.sql
Update TOP(10) UpdateTest4
Set Col1 = 5;
Snowflake
OUT -> SqlServer_06.sql
Update
!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - TOP CLAUSE NOT SUPPORTED ***/!!!
TOP(10) UpdateTest4
Set
Col1 = 5;
WITH TABLE HINT LIMITED
The Update WITH clause in not supported by Snowflake.
SQL Server
IN -> SqlServer_07.sql
Update UpdateTest5 WITH(TABLOCK)
Set Col1 = 5;
Snowflake
OUT -> SqlServer_07.sql
Update UpdateTest5
Set
Col1 = 5;
Related EWIs
The conversion for a regular UPDATE statement is very straightforward. Since the basic UPDATE structure is supported by default in Snowflake, the outliers are the parts where you are going to see some differences, check them in the section.