How to update table using a CTE in snowflake

Updating data in snowflake is similar to any sql language but yet complex task, especially when you need to modify large datasets with multiple conditions. One of the most efficient way to do this task by leveraging Common Table Expressions (CTEs). CTEs will simplify the complex queries, allowing cleaner, more readable code while also improving performance. In this blog post, we’ll explore how to use CTEs to update data in snowflake, highlighting the key benefits and providing step-by-step examples to help you get started.

Update syntax:

# This is the snowflake update syntax
# Fields which are included in between square brackets ([]) are optional in snowflake.

UPDATE <TARGET_TABLE_NAME>
      SET <COL_NAME> = <VALUE> [, <COL_NAME> = <VALUE>,...]
  [ FROM <SOURCE_TABLE_NAME> ]
  [ WHERE <CONDITION> ]

Let’s create some sample table in snowflake, here i am taking example of Employee data, you can relate this scenarios to your actual data or table. If you need any suggestions or help, let me know in the comments.

Here is the CSV file that i have converted into snowflake table:

EmployeeID,FirstName,LastName,Department,Salary,City,ManagerID,JoiningDate
101,John,Doe,IT,75000,San Francisco,501,2020-03-15
102,Jane,Smith,HR,65000,New York,502,2019-07-10…