Search code examples
joinsql-updatesnowflake-cloud-data-platform

Update records on a table based on IDs from another table

SALES table

ID DATEMODIFIED
100 4/25/2023
200 4/27/2023

LINES table

ID TRANSACTION DATEMODIFIED
1 100 null
2 100 null
3 100 null
4 200 null
5 200 null

I am not entirely sure on the appropriate way to join to update DATEMODIFED column on LINES table. The statement needs to update rows on LINES table where TRANSACTION = 100 with 4/25/2023 and TRANSACTION = 200 with 4/27/2023, etc. Is this correct and most efficient? There are millions of rows in both tables.

UPDATE a
SET a.DATEMODIFIED = b.DATEMODIFIED 
FROM LINE AS a
INNER JOIN SALES AS b ON a.TRANSACTION = b.ID
Solution

  • snowflake has another syntax see manual

    UPDATE LINE 
SET LINE.DATEMODIFIED = b.DATEMODIFIED 
FROM 
SALES AS b ON LINE.TRANSACTION = b.ID