Conquer Complex Updates: Mastering SQL Server's UPDATE FROM in Your Snowflake Migration
Updating data within your database might seem straightforward with simple, single-value modifications. However, the reality of data manipulation often involves intricate scenarios: updating multiple columns based on information from other tables, joining various data sets to determine new values, and even navigating complex joins with aliases.
For data engineers and related professionals, the underlying logic of these operations is familiar. The real challenge arises during database modernization. When migrating to a new platform like Snowflake, the lack of direct grammatical compatibility for complex statements such as SQL Server's UPDATE FROM can lead to significant manual effort and potential roadblocks.
So, what happens when your migration plan encounters these non-straightforward syntax elements? Let's delve into how to effectively tackle the migration of SQL Server's UPDATE FROM statement to Snowflake.
Understanding the Nuances: Aliases in UPDATE FROM
It's crucial to clarify the specific pattern we'll be addressing: the UPDATE FROM statement in SQL Server that includes an alias in the SELECT part, where this alias refers to a table involved in the subsequent joins. This seemingly subtle detail introduces complexities during migration.
Let's review the fundamental syntax for both SQL Server and Snowflake:
SQL Server UPDATE syntax:
UPDATE [table_name]
SET column_name = expression [, ...]
[FROM <table_source> [, ...]]
[WHERE <search_condition>]
[OPTION (query_hint)]On the other hand, this is the Snowflake UPDATE syntax:
UPDATE <target_table>
SET <col_name> = <value> [ , <col_name> = <value> , ... ]
[ FROM <additional_tables> ]For more comprehensive details, review Snowflake and SQL Server documentation.
Challenges in migrating UPDATE FROM statements
Migrating UPDATE statements that incorporate joins presents several key challenges:
Lack of direct support for joins (with this pattern): Snowflake's
UPDATEstatement doesn't directly support joining the to-be-updated table with other tables in the same way SQL Server'sFROMclause allows. This necessitates a different approach for translating such logic.Handling aliases and multiple joins: SQL Server's flexibility with table aliasing and multiple joins within an
UPDATEstatement requires careful translation in Snowflake in order to maintain the correct data relationships without compromising integrity.Complex logical conditions: SQL Server
UPDATEstatements can include intricateWHEREclauses and join conditions. Accurately migrating these logical filters to Snowflake is crucial to ensure the correct rows are updated.
We'll focus on addressing these challenges by examining common patterns:
Single and multiple equality conditions
Single and multiple logical comparisons
Logical conditions evaluated over a literal
Evaluation over single and multiple qualified names and literal
IN predicate
Bridging the gap: Migrated code samples in Snowflake
Let’s look at practical examples demonstrating how to translate SQL Server UPDATE statements with LEFT JOIN and RIGHT JOIN clauses to Snowflake.
Set up sample source
SQL Server
CREATE TABLE GenericTable1 (
Col1 INT,
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10),
Col6 VARCHAR(100)
);
CREATE TABLE GenericTable2 (
Col1 VARCHAR(10),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10)
);
CREATE TABLE GenericTable3 (
Col1 VARCHAR(10),
Col2 VARCHAR(100),
Col3 CHAR(1)
);
INSERT INTO GenericTable1 (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES
(1, 'A1', 'B1', 'C1', NULL, NULL),
(2, 'A2', 'B2', 'C2', NULL, NULL),
(3, 'A3', 'B3', 'C3', NULL, NULL);
INSERT INTO GenericTable2 (Col1, Col2, Col3, Col4, Col5)
VALUES
('1', 'A1', 'B1', 'C1', 'X1'),
('2', 'A2', 'B2', 'C2', 'X2'),
('3', 'A3', 'B3', 'C3', 'X3');
INSERT INTO GenericTable3 (Col1, Col2, Col3)
VALUES
('X1', 'Description1', 'A'),
('X2', 'Description2', 'A'),
('X3', 'Description3', 'A');Snowflake
CREATE OR REPLACE TABLE GenericTable1 (
Col1 INT,
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10),
Col6 VARCHAR(100)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "12/18/2024", "domain": "test" }}'
;
CREATE OR REPLACE TABLE GenericTable2 (
Col1 VARCHAR(10),
Col2 VARCHAR(10),
Col3 VARCHAR(10),
Col4 VARCHAR(10),
Col5 VARCHAR(10)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "12/18/2024", "domain": "test" }}'
;
CREATE OR REPLACE TABLE GenericTable3 (
Col1 VARCHAR(10),
Col2 VARCHAR(100),
Col3 CHAR(1)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "transact", "convertedOn": "12/18/2024", "domain": "test" }}'
;
INSERT INTO GenericTable1 (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES
(1, 'A1', 'B1', 'C1', NULL, NULL),
(2, 'A2', 'B2', 'C2', NULL, NULL),
(3, 'A3', 'B3', 'C3', NULL, NULL);
INSERT INTO GenericTable2 (Col1, Col2, Col3, Col4, Col5)
VALUES
('1', 'A1', 'B1', 'C1', 'X1'),
('2', 'A2', 'B2', 'C2', 'X2'),
('3', 'A3', 'B3', 'C3', 'X3');
INSERT INTO GenericTable3 (Col1, Col2, Col3)
VALUES
('X1', 'Description1', 'A'),
('X2', 'Description2', 'A'),
('X3', 'Description3', 'A');Migration sample
SQL Server
UPDATE T1
SET
T1.Col5 = T2.Col5,
T1.Col6 = T3.Col2
FROM GenericTable1 T1
LEFT JOIN GenericTable2 T2 ON
T2.Col1 COLLATE SQL_Latin1_General_CP1_CI_AS = T1.Col1
AND T2.Col2 = T1.Col2
AND T2.Col3 = T1.Col3
AND T2.Col4 = T1.Col4
LEFT JOIN GenericTable3 T3 ON
T3.Col1 = T2.Col5 AND T3.Col3 = 'A';Output without query applied
Output with query applied
Snowflake
UPDATE dbo.GenericTable1 T1
SET
T1.Col5 = T2.Col5,
T1.Col6 = T3.Col2
FROM
GenericTable2 T2,
GenericTable3 T3
WHERE
T2.Col1(+) COLLATE 'EN-CI-AS' = T1.Col1
AND T2.Col2(+) = T1.Col2
AND T2.Col3(+) = T1.Col3
AND T2.Col4(+) = T1.Col4
AND T3.Col1(+) = T2.Col5
AND T3.Col3 = 'A';Output without query applied
Output with query applied
Note: The (+) operator in the Snowflake query is a crucial element for mimicking the LEFT JOIN behavior in the context of an UPDATE statement. The COLLATE 'EN-CI-AS' clause from SQL Server is also translated to Snowflake's collation specification.
SnowConvert: Automating the intricacies of a migration
Notice that the SQL Server example uses aliases within the FROM clause and joins multiple tables to update GenericTable1. Snowflake's UPDATE syntax requires a different approach to achieve the same outcome.
SnowConvert provides an automated solution to handle these complex scenarios. It intelligently utilizes the (+) operator in the WHERE clause to logically define the join conditions without explicitly using the JOIN keyword in the FROM clause of the UPDATE statement. This operator is vital for specifying which columns from the joined tables should be considered for the update.
Here's how SnowConvert helps ensure accurate translation:
Intelligent placement of
(+): SnowConvert automatically detects the join conditions and places the (+) operator on the appropriate columns in theWHEREclause to replicate the behavior of theLEFT JOIN(orRIGHT JOIN, depending on the original SQL Server query).Handling nested logical conditions: The tool parses and translates complex
WHEREclauses and join conditions, enabling all logical filters to be correctly applied in the Snowflake statement.Maintaining data integrity: By precisely translating the update logic, SnowConvert helps ensure that the correct rows are updated in Snowflake, maintaining data consistency and validating that row counts match the source database.
Embrace automation for seamless UPDATE FROM migrations
Migrating SQL Server's UPDATE FROM statements, especially those involving aliases and multiple joins, can be a complex undertaking. While understanding the syntax differences between SQL Server and Snowflake is crucial, relying on manual translation can be time-consuming and error-prone.
SnowConvert offers a powerful and automated solution, simplifying the migration of even the most intricate UPDATE statements. By intelligently handling the nuances of SQL Server's FROM clause and translating them effectively into Snowflake's UPDATE syntax, SnowConvert allows you to shift your focus from tedious code conversion to leveraging the full potential of your Snowflake environment, accelerating your modernization journey and driving business value.
Author
