UPDATE with JOIN
Translation specification for UPDATE statement with WHERE and JOIN clauses
This is a work in progress and may change in the future.
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
SQL Server JOIN type | Snowflake Best Alternative |
---|---|
Single | Use the target table in the |
Multiple | Use the target table in the |
Multiple | Use subquery + IN Operation |
Single | Use subquery + IN Operation |
Multiple | Use Snowflake
|
Multiple | Use Snowflake
|
Single RIGHT JOIN | Use the table in the |
Note-1: Simple JOIN may use the table in the FROM
clause and add filters in the WHERE
clause as needed.
Note-2: Other approaches may include (+) operand to define the JOINs.