Approve if Im right:
1. Fact has no dups and `upd_flag` = {'U', 'I'} (ideally)
2. Stage has no dups
3. If join key
- MATCH then <f_inventory.maint_date = stage_f_inventory.maint_date> and <f_inventory.upd_flag = 'U'>
- NOT MATCH then <f_inventory.maint_date = stage_f_inventory.maint_date> and <f_inventory.upd_flag = 'I'>
from my understanding, query should be like follow:
Code: Select all
UPDATE non_sales.f_inventory
SET maint_date = non_sales.stage_f_inventory.maint_date,
upd_flag = 'U'
FROM non_sales.stage_f_inventory
WHERE (non_sales.f_inventory.week_gen_key = non_sales.stage_f_inventory.week_gen_key)
AND (non_sales.f_inventory.item_gen_key = non_sales.stage_f_inventory.item_gen_key)
AND (non_sales.f_inventory.store_gen_key = non_sales.stage_f_inventory.store_gen_key);
Code: Select all
SET back_order_u ... WHERE... AND back_order_u IS NOT NULL ;
Code: Select all
SET col... WHERE EXISTS (SELECT col_i, col_j FROM stage_tbl as stg WHERE trg_col_i = stg.col_i)