I have two tables, a stage table called: stg_tbl and a target table called: target_tbl. In stg_tbl there is a column called finance_data that I want to add to the target_tbl. The target_tbl and stg_tbl share two columns: person_id, start_dt. I want to join the finance_data column from the stg_tbl to the target_tbl based on the join keys: person_id, start_dt.
What I've tried so far:
SELECT a.*, b.finance_data
FROM target_tbl AS a
LEFT JOIN stg_tbl AS b
ON a.person_id=b.person_id
AND a.start_dt=b.start_dt
Not sure what I am doing wrong here. But after I execute this and check for not null rows like so:
SELECT * FROM target_tbl WHERE finance_data IS NOT NULL;
I get zero results. So somewhere this data is not being matched/registered.
I am executing this SQL through databricks notebook and have already successfully made a connection to snowflake.
I am executing the code through a cursor like so:
cursor.execute("ALTER TABLE TARGET_TBL ADD COLUMN FINANCE_DATA FLOAT")
cursor.execute("""
SELECT a.*, b.finance_data
FROM target_tbl AS a
LEFT JOIN stg_tbl AS b
ON a.person_id=b.person_id
AND a.start_dt=b.start_dt
""")
CodePudding user response：
If you are looking to populate the newly added finance_data column in target_tbl you need to execute an UPDATE statement. Something like this:
UPDATE target_tbl SET finance_data=stg_tbl.finance_data
FROM stg_tbl
WHERE target_tbl.person_id=stg_tbl.person_id
AND target_tbl.start_dt=stg_tbl.start_dt
CodePudding user response：
Update statement
update target_tbl set finance_data=s.finance_data
from target_tbl t
inner join stg_tbl s on s.person_id=t.person_id and s.start_dt=t.start_dt;