CODEWITHSUNDEEP
snowflake-cloud-data-platform
Py1996
Py1996

Reputation: 239

How to update rows based on one field/column of snowflake tables

I want to update one table using another table on field "Id" such that it wont create duplicates

let say my first table is Table1 and second table is Table2 . I would like to update the row in Table1 from Table2 when the Id is matching

I am aware of using UNION function but this applies to entire columns where I only need to consider a single column. https://docs.snowflake.com/en/sql-reference/operators-query.html#union-all

Example of my Tables

Table1

Id  name    number  value
1   a        8       100
2   b        8       100
3   c        8       100
4   d        8       100

Table2

Id  name    number  value
3   c         8      99
4   d         6      100
5   e         7      100

Expected output

Id  name    number  value
1   a        8       100
2   b        8       100
3   c        8        99
4   d        6       100
5   e        7       100

Please note that in the output table row with Id 3,4 has be updated and new Id 5 is inserted. Can someone help me with the select query to get the desired output?

Upvotes: 2

Views: 1552

Answers (1)

Gokhan Atil
Gokhan Atil

Reputation: 10164

You can use MERGE command:

merge into table1 using table2 on table1.id = table2.id
    when matched then 
        update set table1.name = table2.name, table1.number = table2.number, table1.value = table2.value
    when not matched then 
        insert (Id,name,number,value) values (table2.id, table2.name, table2.number, table2.value);
        
select * from table1;

+----+------+--------+-------+
| ID | NAME | NUMBER | VALUE |
+----+------+--------+-------+
|  5 | e    |      7 |   100 |
|  1 | a    |      8 |   100 |
|  2 | b    |      8 |   100 |
|  3 | c    |      8 |    99 |
|  4 | d    |      6 |   100 |
+----+------+--------+-------+

https://docs.snowflake.com/en/sql-reference/sql/merge.html

If you don't want to update the table, you may use IFNULL and full outer join:

select 
IFNULL( t1.id, t2.id) id,
IFNULL( t2.name, t1.name ) name, 
IFNULL( t2.number, t1.number ) number,
IFNULL( t2.value, t1.value ) value
from table1 t1
full join table2 t2
on t1.id = t2.id;


+----+------+--------+-------+
| ID | NAME | NUMBER | VALUE |
+----+------+--------+-------+
|  1 | a    |      8 |   100 |
|  2 | b    |      8 |   100 |
|  3 | c    |      8 |    99 |
|  4 | d    |      6 |   100 |
|  5 | e    |      7 |   100 |
+----+------+--------+-------+

Upvotes: 3

Related Questions