UPDATE with LEFT and RIGHT JOIN
Translation specification for the UPDATE statement with JOINs.
Partially supported in Snowflake
Description
The pattern UPDATE FROM is used to update data based on data from other tables. This SQLServer documentation provides a simple sample.
Review the following SQL Server syntax from the documentation.
SQL Server Syntax
UPDATE [table_name]
SET column_name = expression [, ...]
[FROM <table_source> [, ...]]
[WHERE <search_condition>]
[OPTION (query_hint)]
table_name
: The table or view you are updating.SET
: Specifies the columns and their new values. TheSET
clause assigns a new value (or expression) to one or more columns.FROM
: Used to specify one or more source tables (like a join). It helps define where the data comes from to perform the update.WHERE
: Specifies which rows should be updated based on the condition(s). Without this clause, all rows in the table would be updated.OPTION (query_hint)
: Specifies hints for query optimization.
Snowflake syntax
The Snowflake syntax can also be reviewed in the Snowflake documentation.
UPDATE <target_table>
SET <col_name> = <value> [ , <col_name> = <value> , ... ]
[ FROM <additional_tables> ]
Required parameters
target_table:
Specifies the table to update.col_name:
Specifies the name of a column intarget_table
. Do not include the table name. E.g.,UPDATE t1 SET t1.col = 1
is invalid.value
:
Specifies the new value to set incol_name
.
Optional parameters
FROM
additional_tables:
Specifies one or more tables to use for selecting rows to update or for setting new values. Note that repeating the target table results in a self-join.WHERE
condition:
The expression that specifies the rows in the target table to update. Default: No value (all rows of the target table are updated)
Translation Summary
As it is explained in the grammar description, there is not straight forward equivalent solution for JOINs inside the UPDATE cluase. For this reason, the approach to transform this statements is to add the operator (+) on the column that logically will add the required data into the table. This operator (+) is added to the cases on which the tables are referenced in the LEFT
/RIGHT
JOIN
section.
Notice that there are other languages that use this operator (+) and the position of the operator may determine the type of join. In this specific case in Snowflake, the position will not determine the join type but the asociation with the logically needed tables and columns will.
Even when there are other alternative as MERGE c;ause or the usages of a CTE; these alternatives tend to turn difficult to read when there are complex queries, and get extensive.
Sample Source Patterns
Setup data
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');
LEFT JOIN
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';
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' /*** SSC-FDM-TS0002 - COLLATION FOR VALUE CP1 NOT SUPPORTED ***/ = 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';
RIGHT JOIN
SQL Server
UPDATE T1
SET
T1.Col5 = T2.Col5
FROM GenericTable2 T2
RIGHT JOIN GenericTable1 T1 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;
Snowflake
UPDATE dbo.GenericTable1 T1
SET
T1.Col5 = T2.Col5
FROM
GenericTable2 T2,
GenericTable1 T1
WHERE
T2.Col1 COLLATE 'EN-CI-AS' /*** SSC-FDM-TS0002 - COLLATION FOR VALUE CP1 NOT SUPPORTED ***/ = T1.Col1
AND T2.Col2 = T1.Col2(+)
AND T2.Col3 = T1.Col3(+)
AND T2.Col4 = T1.Col4(+);
Known Issues
There may be patterns that cannot be translated due to differences in logic.
If your query pattern applies, review non-deterministic rows: "When a FROM clause contains a JOIN between tables (e.g.
t1
andt2
), a target row int1
may join against (i.e. match) more than one row in tablet2
. When this occurs, the target row is called a multi-joined row. When updating a multi-joined row, the ERROR_ON_NONDETERMINISTIC_UPDATE session parameter controls the outcome of the update" (Snowflake documentation).
Last updated