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

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.

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

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)
);

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');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (8, 'Grace Green');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (9, 'Hank Blue');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (10, 'Ivy Red');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (11, 'Jack Grey');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (12, 'Kim Yellow');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (13, 'Leo Purple');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (14, 'Mona Pink');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (15, 'Nathan Orange');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (16, 'Olivia Cyan');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (17, 'Paul Violet');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (18, 'Quincy Brown');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (19, 'Rita Silver');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (20, 'Sam Green');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (21, 'Tina Blue');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (22, 'Ursula Red');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (23, 'Vince Yellow');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (24, 'Wendy Black');
INSERT INTO Customers (CustomerID, CustomerName) VALUES (25, 'Xander White');

-- Insert Product Data
INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Laptop', 999.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (2, 'Smartphone', 499.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (3, 'Tablet', 299.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (4, 'Headphones', 149.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (5, 'Monitor', 199.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (6, 'Keyboard', 49.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (7, 'Mouse', 29.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (8, 'Camera', 599.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (9, 'Printer', 99.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (10, 'Speaker', 129.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (11, 'Charger', 29.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (12, 'TV', 699.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (13, 'Smartwatch', 199.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (14, 'Projector', 499.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (15, 'Game Console', 399.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (16, 'Speaker System', 299.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (17, 'Earphones', 89.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (18, 'USB Drive', 15.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (19, 'External Hard Drive', 79.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (20, 'Router', 89.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (21, 'Printer Ink', 49.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (22, 'Flash Drive', 9.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (23, 'Gamepad', 34.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (24, 'Webcam', 49.99);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (25, 'Docking Station', 129.99);

-- Insert Orders Data
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (1, 1, 1, 2, '2024-11-01');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (2, 2, 2, 1, '2024-11-02');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (3, 3, 3, 5, '2024-11-03');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (4, 4, 4, 3, '2024-11-04');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (5, NULL, 5, 7, '2024-11-05');  -- NULL Customer
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (6, 6, 6, 2, '2024-11-06');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (7, 7, NULL, 4, '2024-11-07');  -- NULL Product
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (8, 8, 8, 1, '2024-11-08');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (9, 9, 9, 3, '2024-11-09');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (10, 10, 10, 2, '2024-11-10');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (11, 11, 11, 5, '2024-11-11');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (12, 12, 12, 2, '2024-11-12');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (13, NULL, 13, 8, '2024-11-13');  -- NULL Customer
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (14, 14, NULL, 4, '2024-11-14');  -- NULL Product
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (15, 15, 15, 3, '2024-11-15');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (16, 16, 16, 2, '2024-11-16');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (17, 17, 17, 1, '2024-11-17');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (18, 18, 18, 4, '2024-11-18');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (19, 19, 19, 3, '2024-11-19');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (20, 20, 20, 6, '2024-11-20');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (21, 21, 21, 3, '2024-11-21');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (22, 22, 22, 5, '2024-11-22');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (23, 23, 23, 2, '2024-11-23');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (24, 24, 24, 4, '2024-11-24');
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate) VALUES (25, 25, 25, 3, '2024-11-25');

Case 1: Single `INNER JOIN` Update

For INNER JOIN, if the table is used inside the FROM statements, it automatically turns into INNER JOIN. Notice that there are several approaches to support JOINs in UPDATE statements in Snowflake. This is one of the simplest patterns to ensure readability.

SQL Server

UPDATE Orders
SET Quantity = 10
FROM Orders O
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerName = 'John Doe';

-- Select the changes
SELECT Orders.CustomerID, Orders.Quantity, Customers.CustomerName
FROM Orders, Customers
WHERE Orders.CustomerID = Customers.CustomerID
AND Customers.CustomerName = 'John Doe';

CustomerID	Quantity	CustomerName
1	10	John Doe

Snowflake

UPDATE Orders O
SET O.Quantity = 10
FROM 
  Customers C
WHERE 
  C.CustomerName = 'John Doe'
  AND O.CustomerID = C.CustomerID;


-- Select the changes
SELECT Orders.CustomerID, Orders.Quantity, Customers.CustomerName
FROM Orders, Customers
WHERE Orders.CustomerID = Customers.CustomerID
AND Customers.CustomerName = 'John Doe';

CustomerID	Quantity	CustomerName
1	10	John Doe

Other approaches:

MERGE INTO

MERGE INTO Orders O
USING Customers C
ON O.CustomerID = C.CustomerID
WHEN MATCHED AND C.CustomerName = 'John Doe' THEN
  UPDATE SET O.Quantity = 10;

IN Operation

UPDATE Orders O
SET O.Quantity = 10
WHERE O.CustomerID IN 
  (SELECT CustomerID FROM Customers WHERE CustomerName = 'John Doe');

Case 2: Multiple `INNER JOIN` Update

SQL Server

UPDATE Orders
SET Quantity = 5
FROM Orders O
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
INNER JOIN Products P ON O.ProductID = P.ProductID
WHERE C.CustomerName = 'Alice Johnson' AND P.ProductName = 'Tablet';

-- Select the changes
SELECT Orders.CustomerID, Orders.Quantity, Customers.CustomerName FROM Orders, Customers
WHERE Orders.CustomerID = Customers.CustomerID
AND Customers.CustomerName = 'Alice Johnson';

CustomerID	Quantity	CustomerName
3	5	Alice Johnson

Snowflake

UPDATE Orders O
SET O.Quantity = 5
FROM Customers C, Products P
WHERE O.CustomerID = C.CustomerID
  AND C.CustomerName = 'Alice Johnson'
  AND P.ProductName = 'Tablet'
  AND O.ProductID = P.ProductID;

-- Select the changes
SELECT Orders.CustomerID, Orders.Quantity, Customers.CustomerName FROM Orders, Customers
WHERE Orders.CustomerID = Customers.CustomerID
AND Customers.CustomerName = 'Alice Johnson';

CustomerID	Quantity	CustomerName
3	5	Alice Johnson

Case 3: Multiple `INNER JOIN` Update with Aggregate Condition

SQL Server

UPDATE Orders
SET Quantity = 6
FROM Orders O
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
INNER JOIN Products P ON O.ProductID = P.ProductID
WHERE C.CustomerID IN (SELECT CustomerID FROM Orders WHERE Quantity > 3)
AND P.Price < 200;

SELECT C.CustomerID, C.CustomerName, O.Quantity, P.Price FROM Orders O
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
INNER JOIN Products P ON O.ProductID = P.ProductID
WHERE C.CustomerID IN (SELECT CustomerID FROM Orders WHERE Quantity > 3)
AND P.Price < 200;

CustomerID	CustomerName	Quantity	Price
11	Jack Grey	6	29.99
18	Quincy Brown	6	15.99
20	Sam Green	6	89.99
22	Ursula Red	6	9.99
24	Wendy Black	6	49.99

Snowflake

UPDATE Orders O
SET Quantity = 6
WHERE O.CustomerID IN (SELECT CustomerID FROM Orders WHERE Quantity > 3)
AND O.ProductID IN (SELECT ProductID FROM Products WHERE Price < 200);

-- Select changes
SELECT C.CustomerID, C.CustomerName, O.Quantity, P.Price FROM Orders O
INNER JOIN Customers C ON O.CustomerID = C.CustomerID
INNER JOIN Products P ON O.ProductID = P.ProductID
WHERE C.CustomerID IN (SELECT CustomerID FROM Orders WHERE Quantity > 3)
AND P.Price < 200;

CustomerID	CustomerName	Quantity	Price
11	Jack Grey	6	29.99
18	Quincy Brown	6	15.99
20	Sam Green	6	89.99
22	Ursula Red	6	9.99
24	Wendy Black	6	49.99

Case 4: Single `LEFT JOIN` Update

SQL Server

UPDATE Orders
SET Quantity = 13
FROM Orders O
LEFT JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerID IS NULL AND O.ProductID = 13;

-- Select the changes
SELECT * FROM orders
WHERE CustomerID IS NULL;

OrderID	CustomerID	ProductID	Quantity	OrderDate
5	null	5	7	2024-11-05
13	null	13	13	2024-11-13

Snowflake

UPDATE Orders
SET Quantity = 13
WHERE OrderID IN (
  SELECT O.OrderID
  FROM Orders O
  LEFT JOIN Customers C ON O.CustomerID = C.CustomerID
  WHERE C.CustomerID IS NULL AND O.ProductID = 13
);


-- Select the changes
SELECT * FROM orders
WHERE CustomerID IS NULL;

OrderID	CustomerID	ProductID	Quantity	OrderDate
5	null	5	7	2024-11-05
13	null	13	13	2024-11-13

This approach in Snowflake will not work because it does not update the necessary rows:

UPDATE Orders O SET O.Quantity = 13 FROM Customers C WHERE O.CustomerID = C.CustomerID AND C.CustomerID IS NULL AND O.ProductID = 13;

Case 5: Multiple `LEFT JOIN` and `RIGHT JOIN` Update

This is a more complex pattern. To translate multiple LEFT JOINs, please review the following pattern:

LEFT JOIN and RIGHT JOIN will depend on the order in the FROM clause.

UPDATE [target_table_name]
SET [all_set_statements]
FROM [all_left_join_tables_separated_by_comma]
WHERE [all_clauses_into_the_ON_part]

SQL Server

UPDATE Orders
SET
    Quantity = C.CustomerID
FROM Orders O
LEFT JOIN Customers C ON C.CustomerID = O.CustomerID
LEFT JOIN Products P ON P.ProductID = O.ProductID
WHERE C.CustomerName = 'Alice Johnson'
  AND P.ProductName = 'Tablet';

SELECT O.OrderID, O.CustomerID, O.ProductID, O.Quantity, O.OrderDate
FROM Orders O
LEFT JOIN Customers C ON C.CustomerID = O.CustomerID
LEFT JOIN Products P ON P.ProductID = O.ProductID
WHERE C.CustomerName = 'Alice Johnson'
  AND P.ProductName = 'Tablet';

OrderID	CustomerID	ProductID	Quantity	OrderDate
3	3	3	3	2024-11-12

Snowflake

UPDATE Orders O
SET O.Quantity = C.CustomerID
FROM Customers C, Products P
WHERE O.CustomerID = C.CustomerID
  AND C.CustomerName = 'Alice Johnson'
  AND P.ProductName = 'Tablet'
  AND O.ProductID = P.ProductID;

  SELECT O.OrderID, O.CustomerID, O.ProductID, O.Quantity, O.OrderDate
FROM Orders O
LEFT JOIN Customers C ON C.CustomerID = O.CustomerID
LEFT JOIN Products P ON P.ProductID = O.ProductID
WHERE C.CustomerName = 'Alice Johnson'
  AND P.ProductName = 'Tablet';

OrderID	CustomerID	ProductID	Quantity	OrderDate
3	3	3	3	2024-11-12

Case 6: Mixed `INNER JOIN` and `LEFT JOIN` Update

SQL Server

UPDATE Orders
SET Quantity = 4
FROM Orders O
INNER JOIN Products P ON O.ProductID = P.ProductID
LEFT JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerID IS NULL AND P.ProductName = 'Monitor';

-- Select changes
SELECT O.CustomerID, C.CustomerName, O.Quantity FROM Orders O
INNER JOIN Products P ON O.ProductID = P.ProductID
LEFT JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerID IS NULL AND P.ProductName = 'Monitor';

CustomerID	CustomerName	Quantity
null	null	4

Snowflake

UPDATE Orders O
SET Quantity = 4
WHERE O.ProductID IN (SELECT ProductID FROM Products WHERE ProductName = 'Monitor')
AND O.CustomerID IS NULL;

-- Select changes
SELECT O.CustomerID, C.CustomerName, O.Quantity FROM Orders O
INNER JOIN Products P ON O.ProductID = P.ProductID
LEFT JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerID IS NULL AND P.ProductName = 'Monitor';

CustomerID	CustomerName	Quantity
null	null	4

Case 7: Single `RIGHT JOIN` Update

SQL Server

UPDATE O
SET O.Quantity = 1000
FROM Orders O
RIGHT JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerName = 'Alice Johnson';

-- Select changes 
SELECT
    O.OrderID,
    O.CustomerID,
    O.ProductID,
    O.Quantity,
    O.OrderDate,
    C.CustomerName
FROM
    Orders O
RIGHT JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE
    C.CustomerName = 'Alice Johnson';

OrderID	CustomerID	ProductID	Quantity	CustomerName
3	3	3	1000	Alice Johnson

Snowflake

UPDATE Orders O
SET O.Quantity = 1000
FROM Customers C
WHERE O.CustomerID = C.CustomerID
  AND C.CustomerName = 'Alice Johnson';


  -- Select changes 
SELECT
    O.OrderID,
    O.CustomerID,
    O.ProductID,
    O.Quantity,
    O.OrderDate,
    C.CustomerName
FROM
    Orders O
RIGHT JOIN Customers C ON O.CustomerID = C.CustomerID
WHERE
    C.CustomerName = 'Alice Johnson';

OrderID	CustomerID	ProductID	Quantity	CustomerName
3	3	3	1000	Alice Johnson

Know Issues

Since UPDATE in Snowflake does not allow the usage of JOINs directly, there may be cases that do not match the patterns described.

Previous migration reference

UPDATE with FROM clause and JOIN

However, some changes are made in cases where the UPDATE statement has a FROM clause that contains JOINs since Snowflake does not allow the usage of JOINs with the table being updated. These changes are as follows:

All the joined elements (either tables or subqueries) are moved to the list of FROM elements
All the expressions that come inside the ON clauses after each JOIN are moved to a WHERE clause, joined by AND. If a WHERE clause already exists in the statement, the original expressions of the WHERE clause are preserved at the end of that WHERE clause, while the expressions moved from the ON clause are added at the beginning.
Depending on if the JOIN was LEFT JOIN or RIGHT JOIN, the expression to the left or right of the conditional operation will be included as a way to replicate the functionality of the original JOIN.
- LEFT JOIN: The right side of the conditional is modified.
- RIGHT JOIN: The left side of the conditional is modified

SQL Server

IN -> SqlServer_02.sql

UPDATE
  test_sales.commissions
SET
  test_sales.commissions.commission = 5
FROM
  test_sales.commissions c
LEFT JOIN
  test_sales.targets t
ON
  c.target_id = t.target_id
RIGHT JOIN
	(SELECT * FROM ATABLE) a
ON
	c.target_id = a.target_id;

Snowflake

OUT -> SqlServer_02.sql

UPDATE test_sales.commissions c
	SET
		test_sales.commissions.commission = 5
	FROM
		test_sales.targets t,
		(SELECT
				*
			FROM
				ATABLE
		) a
	WHERE
		test_sales.commissions.target_id = t.target_id(+)
		AND test_sales.commissions.target_id(+) = a.target_id;

PreviousUpdate NextSelect

Last updated 8 days ago

Description

SQL Server Syntax

Snowflake syntax

Translation Summary

Sample Source Patterns

Setup data

Case 1: Single INNER JOIN Update

SQL Server

Snowflake

Case 2: Multiple INNER JOIN Update

SQL Server

Snowflake

Case 3: Multiple INNER JOIN Update with Aggregate Condition

SQL Server

Snowflake

Case 4: Single LEFT JOIN Update

SQL Server

Snowflake

Case 5: Multiple LEFT JOIN and RIGHT JOIN Update

SQL Server

Snowflake

Case 6: Mixed INNER JOIN and LEFT JOIN Update

SQL Server

Snowflake

Case 7: Single RIGHT JOIN Update

SQL Server

Snowflake

Know Issues

UPDATE with FROM clause and JOIN

SQL Server

Case 1: Single `INNER JOIN` Update

Case 2: Multiple `INNER JOIN` Update

Case 3: Multiple `INNER JOIN` Update with Aggregate Condition

Case 4: Single `LEFT JOIN` Update

Case 5: Multiple `LEFT JOIN` and `RIGHT JOIN` Update

Case 6: Mixed `INNER JOIN` and `LEFT JOIN` Update

Case 7: Single `RIGHT JOIN` Update