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.
Snowflake Update Join Syntax
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;
Related Articles,
- How to use Conditional Insert into Snowflake Tables?
- How to Update JSON field in Snowflake Variant Column?
- Snowflake Join Types and Examples
- Snowflake Merge Statement Syntax, Usage and Examples
- How Snowflake Internally Handles Updates?
Hope this helps 🙂