Update

Translation reference to convert SQL Server Update statement to Snowflake

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

Update UpdateTest1
Set Col1 = 5;

Snowflake

Update MYDB.PUBLIC.UpdateTest1
Set Col1 = 5;

UPDATE with FROM clause and JOIN

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

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

UPDATE
    MYDB.test_sales.commissions
SET
    test_sales.commissions.commission = 5
FROM
    MYDB.test_sales.commissions c,        -- Originally in FROM
    MYDB.test_sales.targets t,            -- Comes from first (LEFT) join
    (SELECT * FROM MYDB.PUBLIC.ATABLE) a  -- Comes from second (RIGHT) join
WHERE
    c.target_id = t.target_id(+) AND      -- Comes from first (LEFT) join
    c.target_id(+) = a.target_id;         -- Comes from second (RIGHT) join

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

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

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 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

Update UpdateTest2
Set Col1 = 5
OUTPUT
	deleted.Col1,
	inserted.Col1
	into ValuesTest;

Snowflake

Update MYDB.PUBLIC.UpdateTest2
Set Col1 = 5
-- ** MSC-ERROR - MSCEWI1021 - OUTPUT CLAUSE NOT SUPPORTED **
--OUTPUT deleted.Col1, inserted.Col1
--into ValuesTest
               ;

CTE

The WITH CTE clause is not supported by Snowflake, the complete UPDATE statement is commented out.

SQL Server

With ut as (select * from UpdateTest3)
Update x
Set Col1 = 5 
from ut as x;

Snowflake

-- ** MSC-ERROR - MSCEWI1021 - WITH CTE NOT SUPPORTED **
--With ut as (select * from MYDB.PUBLIC.UpdateTest3)
--Update MYDB.PUBLIC.x Set Col1 = 5
--from MYDB.PUBLIC.ut as x

TOP clause

The TOP clause is not supported by Snowflake.

SQL Server

Update TOP(10) UpdateTest4
Set Col1 = 5;

Snowflake

Update
-- ** MSC-ERROR - MSCEWI1021 - TOP CLAUSE NOT SUPPORTED **
--       TOP(10)
               MYDB.PUBLIC.UpdateTest4
Set Col1 = 5;

WITH TABLE HINT LIMITED

The Update WITH clause in not supported by Snowflake.

SQL Server

Update UpdateTest5 WITH(TABLOCK)
Set Col1 = 5;

Snowflake

Update MYDB.PUBLIC.UpdateTest5
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH TABLE HINT LIMITED - THIS IS NON-RELEVANT **
--                                WITH(TABLOCK)
Set Col1 = 5;

Related EWIs

1. MSCEWI1021: OUTPUT CLAUSE NOT SUPPORTED

2. MSCEWI1021: WITH CTE NOT SUPPORTED

3. MSCEWI1021: TOP CLAUSE NOT SUPPORTED

4. MSCEWI1042: Commented WITH TABLE HINT LIMITED - THIS IS NON-RELEVANT