Snowflake Update Join Syntax

In the database, data is stored in the tables. Data is collected from various sources. Data is collected over the specific period of time and it may or may not be accurate at the time of loading. In some cases, you may want to update the table by taking data from other another table over same or other database on the same server. In this article, we will check Snowflake Update Join Syntax and example on how to update a table with data from another table.

Snowflake Update Join Table

The process of updating tables with the data stored in another table is not much different from the databases. The databases such as Netezza, Redshift, Greenplum supports the updating table using join condition.

Following piece of code is the update table using another table.

UPDATE <target_table>
       SET <col_name> = <value> [ , <col_name> = <value> , ... ]
        [ FROM <additional_tables> ]
        [ WHERE <condition> ]

Note that, there is no specific join clause in the update statement. You have to provide the joining condition in WHERE clause.

Test Data

We will be using the following tables to test update join functionality.

create table update_test1 (col1 int, col2 varchar(20)) ; 

insert into update_test1 values(1,'abc'),(2,'bcd'),(3,'cde');

+------+------+
| COL1 | COL2 |
|------+------|
|    1 | abc  |
|    2 | bcd  |
|    3 | cde  |
+------+------+

create table update_test2 (col1 int, col2 varchar(20)) ; 

insert into update_test2 values(1,'pqr'),(2,'xyz'),(3,'yzx');

+------+------+
| COL1 | COL2 |
|------+------|
|    1 | pqr  |
|    2 | xyz  |
|    3 | yzx  |
+------+------+

Update Join Examples

We have seen update using join syntax in the previous section. Let us use the same in our examples.

Following Update statement uses records from update_test2 to update update_test1 table.

UPDATE update_test1 t1
SET COL2=T2.COL2
FROM update_test2 t2
WHERE t1.col1=t2.col1;

+------+------+
| COL1 | COL2 |
|------+------|
|    1 | pqr  |
|    2 | xyz  |
|    3 | yzx  |
+------+------+

Snowflake Update Table using CASE Condition

You may get requirement to test the values before updating the target table, that is when the CASE conditional option comes into the picture.

For example, consider below example that uses a CASE statement to validate values before updating.

UPDATE update_test1 t1
SET COL2=(CASE WHEN t1.col2 = t2.col2 THEN t1.col2 ELSE t2.col2 END)
FROM update_test2 t2
WHERE t1.col1=t2.col1;

Snowflake Update Table using Multiple Tables

Using multiple tables to update the source table is a common requirement. The Snowflake update command does not support join clause. So, the other workaround would be to create sub query within the FROM clause. You can join multiple tables within your subquery.

For example, consider below update statement with multiple tables. In other word, you can consider it as a multiple join condition in the update statement.

UPDATE update_test1 t1 
SET    col2 = ( CASE 
                  WHEN t1.col2 = t2.col2 THEN t1.col2 
                  ELSE t2.col2 
                END ) 
FROM   (SELECT tb1.col1, 
               tb1.col2 
        FROM   update_test2 AS tb1 
               JOIN update_test1 AS tb2 
                 ON ( tb1.col1 = tb2.col1 )) t2 
WHERE  t1.col1 = t2.col1;