Update Statement

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

See Update statement

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

-- Case 1, THERE IS A REFERENCE TO TABLE2 IN THE SET CLAUSE WITHOUT A FROM
 
 UPDATE CRASHDUMPS.TABLE1 i
 SET COLUMN4 = CRASHDUMPS.TABLE2.COLUMN3
 WHERE i.COLUMN1 = CRASHDUMPS.TABLE2.COLUMN1
 AND i.COLUMN3 = 'L';

 -- CASE 2, FORWARD ALIAS
 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

-- Case 1, THERE IS A REFERENCE TO TABLE2 IN THE SET CLAUSE WITHOUT A FROM
UPDATE CRASHDUMPS.PUBLIC.TABLE1 AS i
SET i.COLUMN4 = CRASHDUMPS.TABLE2.COLUMN3
WHERE i.COLUMN1 = CRASHDUMPS.TABLE2.COLUMN1
AND i.COLUMN3 = 'L';

-- CASE 2, FORWARD ALIAS
UPDATE CRASHDUMPS.PUBLIC.TABLE1 AS i 
SET i.COLUMN4 = CRASHDUMPS.TABLE2.COLUMN3 FROM CRASHDUMPS.PUBLIC.TABLE2
WHERE i.COLUMN1 = CRASHDUMPS.TABLE2.COLUMN1
AND i.COLUMN3 = 'L';

Related EWIs

No related EWIs.