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
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.
Snowflake does not support JOINs
in UPDATE
clause.
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
LEFT JOIN
SQL Server
Snowflake
RIGHT JOIN
SQL Server
Snowflake
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