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

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. The SET 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.

UPDATE <target_table>
       SET <col_name> = <value> [ , <col_name> = <value> , ... ]
        [ FROM <additional_tables> ]
        [ WHERE <condition> ]

Required parameters

• target_table:Specifies the table to update.

• col_name:Specifies the name of a column in target_table. Do not include the table name. E.g., UPDATE t1 SET t1.col = 1 is invalid.

• value:Specifies the new value to set in col_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 INNER JOIN	Use the target table in the FROM clause to emulate an INNER JOIN.
Multiple INNER JOIN	Use the target table in the FROM clause to emulate an INNER JOIN.
Multiple INNER JOIN + Agregate condition	Use subquery + IN Operation
Single LEFT JOIN	Use subquery + IN Operation
Multiple LEFT JOIN	Use Snowflake UPDATE reordering the statements as needed. UPDATE [target_table_name] SET [all_set_statements] FROM [all_left_join_tables_separated_by_comma] WHERE [all_clauses_into_the_ON_part]
Multiple RIGHT JOIN	Use Snowflake UPDATE reordering the statements as needed. UPDATE [target_table_name] SET [all_set_statements] FROM [all_right_join_tables_separated_by_comma] WHERE [all_clauses_into_the_ON_part]
Single RIGHT JOIN	Use the table in the FROM clause and add filters in the WHERE clause as needed.

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.

Sample Source Patterns

Setup data

SQLServer

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    OrderDate DATE
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

Snowflake

CREATE OR REPLACE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    Quantity INT,
    OrderDate DATE
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/12/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/12/2024",  "domain": "test" }}'
;

CREATE OR REPLACE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "transact",  "convertedOn": "11/12/2024",  "domain": "test" }}'
;

Data Insertion for samples

-- Insert Customer Data
INSERT INTO Customers (CustomerID, CustomerName) VALUES (1, 'John Doe');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (2, 'Jane Smith');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (3, 'Alice Johnson');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (4, 'Bob Lee');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (5, 'Charlie Brown');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (6, 'David White');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (7, 'Eve Black');