合并为不更新具有需要唯一的相同非pk值的行

Question

帮助！！

我写了一个查询，将视图中的数据合并到数据仓库表中

该视图包含来自3个不同数据库的数据

如果TFN(税务文件号，而不是pk(是相同的，我需要更新数据仓库中的一行

然而，只有2个表(在澳大利亚(存储TFN，第三个表(不在澳大利亚(不存储，并且这些行的视图中的TFN为空

我尝试了一些东西，但没有得到结果。我在追求

此尝试运行正常，但插入具有相同TFN 的第二行

MERGE INTO DWCUSTOMER d
using CUST_ALL_VIEW V
ON ((d.TFN = v.TFN AND d.COUNTRY = 'Australia') OR (d.CUSTOMER_ID = v.CUSTOMER_ID AND d.COUNTRY <> 'Australia'))
when matched then update set D.OCCUPATION = V.OCCUPATION, D.CITY = V.CITY, D.STATE = V.STATE,
d.POSTAL_CODE = v.POSTAL_CODE, d.REGION = v.REGION, d.COUNTRY = v.COUNTRY
when not matched then insert
(CUSTOMER_KEY, CUSTOMER_ID, CITY, STATE, POSTAL_CODE, GENDER, TFN, OCCUPATION, REGION, COUNTRY)
values
(DWCUSTSEQ.NEXTVAL, v.CUSTOMER_ID, v.CITY, v.STATE, v.POSTAL_CODE, v.GENDER, v.TFN, v.OCCUPATION, v.REGION, v.COUNTRY);

我做的另一个尝试是无效的(正如我后来发现的那样，你不能只在许多列中的一列上有一个唯一/不同的(，但它更好地展示了我试图实现的目标(我认为：/(

merge into DWCUSTOMER D
using (select SOURCE_ROWID, TABLE_NAME, CUSTOMER_ID, CITY, STATE,
POSTAL_CODE, GENDER, REGION, COUNTRY, UNIQUE(TFN) AS TFN, OCCUPATION
from CUST_ALL_VIEW
where COUNTRY = 'Australia'
UNION
select *
from CUST_ALL_VIEW
WHERE COUNTRY <> 'Australia') v
on ((D.TFN = V.TFN and D.COUNTRY = 'Australia') or (D.CUSTOMER_ID = V.CUSTOMER_ID and D.COUNTRY <> 'Australia'))
when matched then update set D.OCCUPATION = V.OCCUPATION, D.CITY = V.CITY, D.STATE = V.STATE,
d.POSTAL_CODE = v.POSTAL_CODE, d.REGION = v.REGION, d.COUNTRY = v.COUNTRY
when not matched then insert
(CUSTOMER_KEY, CUSTOMER_ID, CITY, STATE, POSTAL_CODE, GENDER, TFN, OCCUPATION, REGION, COUNTRY)
values
(DWCUSTSEQ.NEXTVAL, v.CUSTOMER_ID, v.CITY, v.STATE, v.POSTAL_CODE, v.GENDER, v.TFN, v.OCCUPATION, v.REGION, v.COUNTRY);

要修复的建议？？

感谢

Matt

Answer 1

(最晚的答案是late(尝试将TFN更改为：

ISNULL(d.TFN,-9) = isnull(v.TFN,-9)

(其中-9显然是一个无效的TFN-只是为了绕过SQL Null比较的奥秘(