Oracle SQL, Combine Update or Merge with Join, Insert Surrogate Keys into Fact table using 2 dimensions tables

Question

I have populated my dimension tables (Oracle SQL Dev.) For the next step I created the facts table (F_Orders), I loaded quantity, price , the Order_ID's and surrogate keys into the facts table.

I want to Insert the latest Surrogate_ID's (customer, location, etc.) with the specific record. In SA_ORDERS I can see which Order_ID is connected with which Customer_ID. Using Customer_ID in D_CUSTOMERS I can find the Surrogate key.

To perform this task, I used this query:

INSERT INTO F_Orders 
(order_id
,quanity
,price
,cust_s_key)
       
SELECT
sa_order_items.order_id
,sa_order_items.quantity
,sa_order_items.unit_price
,d_customers.s_key
FROM
sa_order_items
JOIN sa_orders ON sa_order_items.order_id = sa_orders.order_id
JOIN d_customers ONd_customers.customer_id=sa_orders.customer_id
WHERE 
d_customers."Latest" = 'Y'
AND d_customers.flag = 'I'

Now I was wondering, can I perform update for Surrogate key (cust_s_key) as I have 2 joints to use? Unfortunately, I cant combine update with the join successfully. How could I combine both or may find another way to do it? Thanks a lot in advance. PS. I know that in the ETL we won't need this update often.

Answer 1

we can update the fact table using the same query

UPDATE F_Orders fo SET fo.cust_s_key = (SELECT
distinct d_customers.s_key
FROM
sa_order_items
JOIN sa_orders ON sa_order_items.order_id = sa_orders.order_id
JOIN d_customers ONd_customers.customer_id=sa_orders.customer_id
WHERE 
d_customers."Latest" = 'Y'
AND d_customers.flag = 'I'
AND sa_order_items.order_id = fo.order_id)
WHERE EXISTS (SELECT 1 FROM
sa_order_items
JOIN sa_orders ON sa_order_items.order_id = sa_orders.order_id
JOIN d_customers ONd_customers.customer_id=sa_orders.customer_id
WHERE 
d_customers."Latest" = 'Y'
AND d_customers.flag = 'I'
AND sa_order_items.order_id = fo.order_id)