UPDATE
Description
Updates values in one or more table columns when a condition is satisfied. (Redshift SQL Language Reference Update Statement).
This syntax is fully supported in Snowflake.
Grammar Syntax
[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
UPDATE table_name [ [ AS ] alias ] SET column = { expression | DEFAULT } [,...]
[ FROM fromlist ]
[ WHERE condition ]Sample Source Patterns
Setup data
CREATE TABLE employees (
id INTEGER IDENTITY(1,1),
name VARCHAR(100),
salary DECIMAL DEFAULT 20000,
department VARCHAR(50) DEFAULT 'Marketing'
);
INSERT INTO employees (name, salary, department)
VALUES
('Alice', 500000, 'HR'),
('Bob', 600000, 'Engineering'),
('Charlie', 700000, 'Engineering'),
('David', 400000, 'Marketing'),
('Eve', 450000, 'HR'),
('Frank', 750000, 'Engineering'),
('Grace', 650000, 'Engineering'),
('Helen', 390000, 'Marketing'),
('Ivy', 480000, 'HR'),
('Jack', 420000, 'Engineering'),
('Ken', 700000, 'Marketing'),
('Liam', 600000, 'Engineering'),
('Mona', 470000, 'HR');
CREATE TABLE department_bonus (
department VARCHAR(100),
bonus DECIMAL
);
INSERT INTO department_bonus (department, bonus)
VALUES
('HR', 10000),
('Engineering', 50000),
('Marketing', 20000),
('Sales', 5000);Alias
Although Snowflake's grammar does not specify that a table alias can be used, it's valid code in Snowflake.
Input Code:
UPDATE employees AS e
SET salary = salary + 5000
WHERE e.salary < 600000;Output Code:
UPDATE employees AS e
SET salary = salary + 5000
WHERE e.salary < 600000;WITH clause
This clause specifies one or more Common Table Expressions (CTE). The output column names are optional for non-recursive CTEs, but mandatory for recursive ones.
Since this clause cannot be used in an UPDATE statement, it is transformed into temporary tables with their corresponding queries. After the UPDATE statement is executed, these temporary tables are dropped to clean up, release resources, and avoid name collisions when creating tables within the same session. Additionally, if a regular table with the same name exists, it will take precedence again, since the temporary table has priority over any other table with the same name in the same session.
Non-Recursive CTE
Input Code:
WITH avg_salary_cte AS (
SELECT AVG(salary) AS avg_salary FROM employees
)
UPDATE employees
SET salary = (SELECT avg_salary FROM avg_salary_cte)
WHERE salary < 500000;Output Code:
CREATE TEMPORARY TABLE avg_salary_cte AS
SELECT AVG(salary) AS avg_salary FROM
employees;
UPDATE employees
SET salary = (SELECT avg_salary FROM
avg_salary_cte
)
WHERE salary < 500000;
DROP TABLE avg_salary_cte;Recursive CTE
Input Code:
WITH RECURSIVE bonus_updates(id, name, department, salary, level) AS (
SELECT e.id,
e.name,
e.department,
e.salary + CASE
WHEN db.bonus IS NOT NULL THEN db.bonus
ELSE 0
END AS new_salary,
1 AS level
FROM employees e
LEFT JOIN department_bonus db ON e.department = db.department
UNION ALL
SELECT e.id,
e.name,
e.department,
e.salary + CASE
WHEN db.bonus IS NOT NULL THEN db.bonus
ELSE 0
END + (e.salary * 0.05) AS new_salary,
bu.level + 1
FROM employees e
JOIN department_bonus db ON e.department = db.department
JOIN bonus_updates bu ON e.id = bu.id
WHERE bu.level < 3
)
UPDATE employees
SET salary = bu.new_salary
FROM (SELECT id, AVG(salary) as new_salary FROM bonus_updates GROUP BY id) as bu
WHERE employees.id = bu.id
AND bu.new_salary > employees.salary;Output Code:
CREATE TEMPORARY TABLE bonus_updates AS
--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS "employees", "department_bonus" **
WITH RECURSIVE bonus_updates(id, name, department, salary, level) AS (
SELECT e.id,
e.name,
e.department,
e.salary + CASE
WHEN db.bonus IS NOT NULL THEN db.bonus
ELSE 0
END AS new_salary,
1 AS level
FROM
employees e
LEFT JOIN
department_bonus db ON e.department = db.department
UNION ALL
SELECT e.id,
e.name,
e.department,
e.salary + CASE
WHEN db.bonus IS NOT NULL THEN db.bonus
ELSE 0
END + (e.salary * 0.05) AS new_salary,
bu.level + 1
FROM
employees e
JOIN
department_bonus db ON e.department = db.department
JOIN
bonus_updates bu ON e.id = bu.id
WHERE bu.level < 3
)
SELECT
id,
name,
department,
salary,
level
FROM
bonus_updates;
UPDATE employees
SET salary = bu.new_salary
FROM (SELECT id, AVG(salary) as new_salary
FROM bonus_updates
GROUP BY id) as bu
WHERE employees.id = bu.id
AND bu.new_salary > employees.salary;
DROP TABLE bonus_updates;SET DEFAULT values
Input Code:
UPDATE employees
SET salary = DEFAULT, department = 'Sales'
WHERE department = 'HR';Output Code:
UPDATE employees
SET salary = DEFAULT, department = 'Sales'
WHERE
department = 'HR';SET clause
It is responsible for modifying values in the columns. Similar to Snowflake, update queries with multiple matches per row will throw an error when the configuration parameter ERROR_ON_NONDETERMINISTIC_UPDATE is set to true. This flag works the same way in Snowflake, and it even uses the same name, ERROR_ON_NONDETERMINISTIC_UPDATE.
However, when this flag is turned off, no error is returned, and one of the matched rows is used to update the target row. The selected joined row is nondeterministic and arbitrary in both languages; the behavior may not be consistent across executions, which could lead to data inconsistencies.
Setup data:
CREATE TABLE target (
k INT,
v INT
);
CREATE TABLE src (
k INT,
v INT
);
INSERT INTO target (k, v) VALUES (0, 10);
INSERT INTO src (k, v) VALUES
(0, 14),
(0, 15),
(0, 16);Input Code:
UPDATE target
SET v = src.v
FROM src
WHERE target.k = src.k;
SELECT * FROM target;Output Code:
UPDATE target
SET v = src.v
FROM src
WHERE target.k = src.k;
SELECT * FROM target;Known Issues
Update queries with multiple matches per row may cause data inconsistencies. Although both platforms have the flag ERROR_ON_NONDETERMINISTIC_UPDATE, these values will always be nondeterministic. Snowflake offers recommendations for handling these scenarios. Click here for more details.
Replicating the functionality of the
WITHclause requires creating temporary tables mirroring each Common Table Expression (CTE). However, this approach fails if a temporary table with the same name already exists within the current session, causing an error.
Related EWIs
There are no known issues.
Last updated