Update

Translation reference to convert SQL Server Update statement to Snowflake

Some parts in the output code are omitted for clarity reasons.

Description

Changes existing data in a table or view in SQL Server. For more information regarding SQL Server Update, check here.

[ WITH <common_table_expression> [...n] ]  
UPDATE   
    [ TOP ( expression ) [ PERCENT ] ]   
    { { table_alias | <object> | rowset_function_limited   
         [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]  
      }  
      | @table_variable      
    }  
    SET  
        { column_name = { expression | DEFAULT | NULL }  
          | { udt_column_name.{ { property_name = expression  
                                | field_name = expression }  
                                | method_name ( argument [ ,...n ] )  
                              }  
          }  
          | column_name { .WRITE ( expression , @Offset , @Length ) }  
          | @variable = expression  
          | @variable = column = expression  
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression  
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression  
          | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression  
        } [ ,...n ]   
  
    [ <OUTPUT Clause> ]  
    [ FROM{ <table_source> } [ ,...n ] ]   
    [ WHERE { <search_condition>   
            | { [ CURRENT OF   
                  { { [ GLOBAL ] cursor_name }   
                      | cursor_variable_name   
                  }   
                ]  
              }  
            }   
    ]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]  
[ ; ]  
  
<object> ::=  
{   
    [ server_name . database_name . schema_name .   
    | database_name .[ schema_name ] .   
    | schema_name .  
    ]  
    table_or_view_name}  

Sample Source Patterns

Basic UPDATE

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 Known Issues section.

SQL Server

IN -> SqlServer_01.sql

Update UpdateTest1
Set Col1 = 5;

Snowflake

OUT -> SqlServer_01.sql

Update UpdateTest1
Set
Col1 = 5;

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 IN SNOWFLAKE ***/!!!
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 IN SNOWFLAKE ***/!!!
-- 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

1. SSC-EWI-0021: Syntax not supported in Snowflake.