SnowflakeDocumentation Home

UPDATE

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

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:

IN -> Redshift_01.sql
UPDATE employees AS e
SET salary = salary + 5000
WHERE e.salary < 600000;

Output Code:

OUT -> Redshift_01.sql
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:

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;

Output Code:

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;

Recursive CTE

Input Code:

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;

Output Code:

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;

SET DEFAULT values

Input Code:

IN -> Redshift_04.sql
UPDATE employees
SET salary = DEFAULT, department = 'Sales'
WHERE department = 'HR';

Output Code:

OUT -> Redshift_04.sql
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:

IN -> Redshift_05.sql
UPDATE target
  SET v = src.v
  FROM src
  WHERE target.k = src.k;


SELECT * FROM target;

Output Code:

OUT -> Redshift_05.sql
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 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.

PreviousMERGENextEXECUTE

Last updated