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

Redshift

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:

Redshift

IN -> Redshift_01.sql

UPDATE employees AS e
SET salary = salary + 5000
WHERE e.salary < 600000;

Result

ID	NAME	SALARY	DEPARTMENT
1	Alice	505000	HR
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	405000	Marketing
5	Eve	455000	HR
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	395000	Marketing
9	Ivy	485000	HR
10	Jack	425000	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	475000	HR

Output Code:

Snowflake

OUT -> Redshift_01.sql

UPDATE employees AS e
SET salary = salary + 5000
WHERE e.salary < 600000;

Result

ID	NAME	SALARY	DEPARTMENT
1	Alice	505000	HR
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	405000	Marketing
5	Eve	455000	HR
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	395000	Marketing
9	Ivy	485000	HR
10	Jack	425000	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	475000	HR

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:

Redshift

IN -> Redshift_02.sql

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;

Result

ID	NAME	SALARY	DEPARTMENT
1	Alice	500000	HR
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	546923	Marketing
5	Eve	546923	HR
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	546923	Marketing
9	Ivy	546923	HR
10	Jack	546923	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	546923	HR

Output Code:

Snowflake

OUT -> Redshift_02.sql

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;

Result

ID	NAME	SALARY	DEPARTMENT
1	Alice	500000	HR
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	546923	Marketing
5	Eve	546923	HR
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	546923	Marketing
9	Ivy	546923	HR
10	Jack	546923	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	546923	HR

Recursive CTE

Input Code:

Redshift

IN -> Redshift_03.sql

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;

Result

ID	NAME	SALARY	DEPARTMENT
1	Alice	526666	HR
2	Bob	670000	Engineering
3	Charlie	773333	Engineering
4	David	433333	Marketing
5	Eve	475000	HR
6	Frank	825000	Engineering
7	Grace	721666	Engineering
8	Helen	423000	Marketing
9	Ivy	506000	HR
10	Jack	484000	Engineering
11	Ken	743333	Marketing
12	Liam	670000	Engineering
13	Mona	495668	HR

Output Code:

Snowflake

OUT -> Redshift_03.sql

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;

Result

ID	NAME	SALARY	DEPARTMENT
1	Alice	526667	HR
2	Bob	670000	Engineering
3	Charlie	773333	Engineering
4	David	433333	Marketing
5	Eve	475000	HR
6	Frank	825000	Engineering
7	Grace	721667	Engineering
8	Helen	423000	Marketing
9	Ivy	506000	HR
10	Jack	484000	Engineering
11	Ken	743333	Marketing
12	Liam	670000	Engineering
13	Mona	495667	HR

SET DEFAULT values

Input Code:

Redshift

IN -> Redshift_04.sql

UPDATE employees
SET salary = DEFAULT, department = 'Sales'
WHERE department = 'HR';

Result

ID	NAME	SALARY	DEPARTMENT
1	Alice	20000	Sales
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	400000	Marketing
5	Eve	20000	Sales
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	390000	Marketing
9	Ivy	20000	Sales
10	Jack	420000	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	20000	Sales

Output Code:

Snowflake

OUT -> Redshift_04.sql

UPDATE employees
SET salary = DEFAULT, department = 'Sales'
WHERE
    department = 'HR';

Result

ID	NAME	SALARY	DEPARTMENT
1	Alice	20000	Sales
2	Bob	600000	Engineering
3	Charlie	700000	Engineering
4	David	400000	Marketing
5	Eve	20000	Sales
6	Frank	750000	Engineering
7	Grace	650000	Engineering
8	Helen	390000	Marketing
9	Ivy	20000	Sales
10	Jack	420000	Engineering
11	Ken	700000	Marketing
12	Liam	600000	Engineering
13	Mona	20000	Sales

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:

Redshift

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:

Redshift

IN -> Redshift_05.sql

UPDATE target
  SET v = src.v
  FROM src
  WHERE target.k = src.k;


SELECT * FROM target;

Result

K	V
0	16

Output Code:

Snowflake

OUT -> Redshift_05.sql

UPDATE target
  SET v = src.v
  FROM src
  WHERE target.k = src.k;


SELECT * FROM target;

Result

K	V
0	14

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 WITH clause 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.