Oracle SQL，结合更新或合并与Join，插入代理键到事实表使用2维表

Question

我已经填充了我的维度表(Oracle SQL Dev))。下一步，我创建了facts table(F_Orders)， I装载数量，价格，Order_ID的和surrogate keys转化为facts table。

我想用特定的record插入最新的Surrogate_ID's(客户，位置等)。在SA_ORDERS我可以看到哪个Order_ID与哪个Customer_ID相连。在D_CUSTOMERS中使用Customer_ID我可以找到Surrogate key.

为了执行这个任务，我使用了这个查询:

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'

现在我想知道，我可以执行updateSurrogate key(custrong_key)，因为我有2joints使用?不幸的是，我不能成功地将update与join结合起来。如何组合或者可以找到另一种方法去做?提前谢谢你。PS.我知道在ETL中我们不会经常需要这个更新。

Answer 1

我们可以使用相同的查询

更新事实表

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)