Adding a Column from Another Table in Snowflake

Question

This is my second day working with snowflake, and I understand the documentation in https://docs.snowflake.net/manuals/sql-reference/sql/alter-table-column.html may cover some of what I'm trying to do, but I'm lost on the terminology.

Basics: I have a table called "Transactions" with a key for Salesreps (Salesrep_Number) and another table called "SALESREPS" two columns called Salesrep_Region and the same Salesrep_Number. I want to add Salesrep_Region from "Sales Reps" to "Transactions" using Salesrep_Number as the join key.

Here is what I tried. First, I created a column for "Salesrep_Region" that was null in "Transactions." Next,

ALTER TABLE "SANDBOX"."AVISMA"."TRANSACTIONS" MODIFY COLUMN SALESREP_REGION

select distinct "SALESREP_REGION, SALESREP_NUMBER" from "SANDBOX"."AVISMA"."SALESREPS"

where "SANDBOX"."AVISMA"."TRANSACTIONS".SALESREP_NUMBER="SANDBOX"."AVISMA"."SALES_REPS".SALESREP_NUMBER

I got an error. It should be simple to simply add a column with a left join without recreating the entire "TRansactions" table. How do I do this?

Mike Walton Mike Walton · Accepted Answer · 2020-03-13T23:34:49

Have you tried using an UPDATE statement, instead of an ALTER TABLE? Once you have the new column in your TRANSACTIONS table, you could simply update:

update TRANSACTIONS t
  set t.SALESREP_REGION = sr.SALESREP_REGION
  from SALESREPS sr
  where t.SALESREP_NUMBER = sr.SALESREP_NUMBER;

This will only work if SALESREP is unique on SALESREP_NUMBER.

Adding a Column from Another Table in Snowflake

1 Answers