Update Statement

SQL statement that modifies column values in existing rows of a table.

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

Modifies column values in existing rows of a table. (Teradata SQL Language Reference UPDATE)

Sample Source Patterns

Basic case

Teradata

Update

IN -> Teradata_01.sql

 UPDATE CRASHDUMPS.TABLE1 i
 SET COLUMN4 = CRASHDUMPS.TABLE2.COLUMN3
 WHERE i.COLUMN1 = CRASHDUMPS.TABLE2.COLUMN1
 AND i.COLUMN3 = 'L';

Snowflake

Update

OUT -> Teradata_01.sql

UPDATE CRASHDUMPS.TABLE1 AS i
 SET
  i.COLUMN4 = CRASHDUMPS.TABLE2.COLUMN3
 FROM
  CRASHDUMPS.TABLE2
  WHERE i.COLUMN1 = CRASHDUMPS.TABLE2.COLUMN1
  AND UPPER(RTRIM( i.COLUMN3)) = UPPER(RTRIM('L'));

UPDATE with forward alias

Teradata supports referencing an alias before it is declared, but Snowflake does not. The transformation for this scenario is to take the referenced table and change the alias for the table name it references.

Teradata

Update

IN -> Teradata_02.sql

 UPDATE i
 FROM CRASHDUMPS.TABLE2, CRASHDUMPS.TABLE1 i
 SET COLUMN4 = CRASHDUMPS.TABLE2.COLUMN3
 WHERE i.COLUMN1 = CRASHDUMPS.TABLE2.COLUMN1
 AND i.COLUMN3 = 'L';

Snowflake

Update

OUT -> Teradata_02.sql

UPDATE CRASHDUMPS.TABLE1 AS i
  SET
  i.COLUMN4 = CRASHDUMPS.TABLE2.COLUMN3
  FROM
  CRASHDUMPS.TABLE2
  WHERE i.COLUMN1 = CRASHDUMPS.TABLE2.COLUMN1
  AND UPPER(RTRIM( i.COLUMN3)) = UPPER(RTRIM('L'));

UPDATE with target table in the the FROM clause

Teradata supports having the target table defined in the FROM clause, this is removed in Snowflake to avoid duplicate alias and ambiguous column reference errors.

Teradata

Update

IN -> Teradata_03.sql

UPDATE some_table
FROM some_table
SET Code = Code + 100
WHERE Name = 'A';

Snowflake

Update

OUT -> Teradata_03.sql

UPDATE some_table
  SET Code = Code + 100
  WHERE
  UPPER(RTRIM( Name)) = UPPER(RTRIM('A'));

Related EWIs

No related EWIs.