sql-server - MERGE 语句包含多部分标识符
问题描述
我写了以下合并指令:
MERGE INTO dbo.sperradressen WITH (HOLDLOCK) AS target
USING (SELECT dbo.f010.*, ISNULL(land.iso2,0) iso2land
FROM dbo.f010
LEFT JOIN dbo.land ON dbo.f010.firma = land.firma
AND dbo.f010.land = dbo.land.nr
AND dbo.land.unr = 0) AS source
ON target.ref_table ='f010' AND target.ref_key = source.key_1
WHEN MATCHED AND (target.name1 != source.name1 COLLATE Latin1_General_CS_AS OR
target.name2 != source.name2 COLLATE Latin1_General_CS_AS OR
target.name3 != source.name3 COLLATE Latin1_General_CS_AS OR
target.strasse != source.str COLLATE Latin1_General_CS_AS OR
target.plz != source.plz COLLATE Latin1_General_CS_AS OR
target.stadt != source.ort COLLATE Latin1_General_CS_AS OR
target.land != source.iso2land)
THEN
UPDATE
SET target.name1 = source.name1,
target.name2 = source.name2,
target.name3 = source.name3,
target.strasse = source.str,
target.plz = source.plz,
target.stadt = source.ort,
target.land = source.iso2land,
target.eusanktstatus = 0,
target.hhupd = '${hhupd}',
target.eusanktinfo = '',
target.info = '',
target.firstname = '',
target.middlename = '',
target.lastname = '',
target.wholename = '',
target.street = '',
target.zipcode = '',
target.city = '',
target.country = ''
WHEN NOT MATCHED BY TARGET THEN
INSERT (target.name1, target.name2, target.name3, target.strasse, target.plz, target.stadt, target.land, target.hhupd)
VALUES (source.name1, source.name2, source.name3,
source.str, source.plz, source.ort, source.iso2land, '${hhupd}')
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
这个效果很好,所以我想创建第二个。
MERGE INTO dbo.sperradressen WITH (HOLDLOCK) AS target
USING (SELECT dbo.f012.*, ISNULL(land.iso2,0) iso2land FROM dbo.f012
INNER JOIN f010 ON f010.firma = f012.firma AND f010.sa = f012.sa AND f010.konto = f012.konto
LEFT JOIN dbo.land on dbo.f012.firma = land.firma AND dbo.land.unr = 0 )
AS source
ON target.ref_table ='f012' AND target.ref_key = source.key_1
WHEN MATCHED AND (target.name1 != source.nachname OR
target.name2 != source.vorname OR
target.name3 != source.adr_zusatz OR
target.strasse != source.str OR
target.plz != f010.plz OR
target.stadt != f010.ort OR
target.land != source.iso2land)
THEN
UPDATE SET
target.name1 = source.nachname,
target.name2 = source.vorname,
target.name3 = source.adr_zusatz,
target.strasse = f010.str,
target.plz = f010.plz,
target.stadt = f010.ort,
target.land = source.iso2land,
target.eusanktstatus = 0,
target.hhupd = '${hhupd}',
target.eusanktinfo = '',
target.info = '',
target.firstname = '',
target.middlename = '',
target.lastname = '',
target.wholename = '',
target.street = '',
target.zipcode = '',
target.city = '',
target.country = ''
WHEN NOT MATCHED BY TARGET THEN
INSERT (target.name1, target.name2, target.name3,target.strasse,target.plz,target.stadt, target.land, target.hhupd)
VALUES (source.nachname, source.vorname, source.adr_zusatz,f010.str,f010.plz,f010.ort,source.iso2land, '${hhupd}')
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
下面是来自 SQL Server 的错误消息:
MERGE 语句中使用的插入列列表不能包含多部分标识符。请改用单部分标识符。
它清楚地用 f010 包围了部分所以这一定是错误所在。但是当我查看原始更新脚本时,我不得不那样使用它,因为不同表的列属性。所以这里是原始脚本:
UPDATE sperradressen
SET Adress.name1 = f012.nachname,
sperradressen.name2 = f012.vorname,
sperradressen.name3 = f012.adr_zusatz,
sperradressen.strasse = f010.str,
sperradressen.plz = f010.plz,
sperradressen.stadt = f010.ort,
sperradressen.land = land.iso2,
sperradressen.eusanktstatus = 0,
sperradressen.hhupd = '${hhupd}',
sperradressen.eusanktinfo = '',
sperradressen.info = '',
sperradressen.firstname = '',
sperradressen.middlename = '',
sperradressen.lastname = '',
sperradressen.wholename = '',
sperradressen.street = '',
sperradressen.zipcode = '',
sperradressen.city = '',
sperradressen.country = ''
FROM f012
INNER JOIN f010 ON ( f010.firma = f012.firma AND f010.sa = f012.sa AND f010.konto = f012.konto )
INNER JOIN land ON ( f010.firma = land.firma AND f010.land = land.nr AND land.unr = 0 )
WHERE f012.firma = ${TBR}
AND f012.anr = 00000000
AND f012.key_1 = sperradressen.key_1
AND (sperradressen.name1 != f012.nachname
OR sperradressen.name2 != f012.vorname
OR sperradressen.name3 != f012.adr_zusatz
OR sperradressen.strasse != f010.str
OR sperradressen.plz != f010.plz
OR sperradressen.stadt != f010.ort
OR sperradressen.land != land.iso2);
现在我如何获得正确的一件式标识符。?
target.strasse != source.str OR
target.plz != f010.plz OR
target.stadt != f010.ort OR
这是红色下划线,但如何使它工作?
解决方案
在您的 INSERT 语句中,您正在指定 f010 表中的值。这是不允许的,您应该将它们添加到 SOURCE 表中。
像这样的东西:
MERGE INTO dbo.sperradressen WITH (HOLDLOCK) AS target
USING (SELECT dbo.f012.*, ISNULL(land.iso2,0) iso2land, f010.str,f010.plz,f010.ort
FROM dbo.f012
INNER JOIN f010 ON f010.firma = f012.firma AND f010.sa = f012.sa AND f010.konto = f012.konto
LEFT JOIN dbo.land on dbo.f012.firma = land.firma AND dbo.land.unr = 0 )
AS source
ON target.ref_table ='f012' AND target.ref_key = source.key_1
WHEN MATCHED AND (target.name1 != source.nachname OR
target.name2 != source.vorname OR
target.name3 != source.adr_zusatz OR
target.strasse != source.str OR
target.plz != f010.plz OR
target.stadt != f010.ort OR
target.land != source.iso2land)
THEN
UPDATE SET
target.name1 = source.nachname,
target.name2 = source.vorname,
target.name3 = source.adr_zusatz,
target.strasse = f010.str,
target.plz = f010.plz,
target.stadt = f010.ort,
target.land = source.iso2land,
target.eusanktstatus = 0,
target.hhupd = '${hhupd}',
target.eusanktinfo = '',
target.info = '',
target.firstname = '',
target.middlename = '',
target.lastname = '',
target.wholename = '',
target.street = '',
target.zipcode = '',
target.city = '',
target.country = ''
WHEN NOT MATCHED BY TARGET THEN
INSERT (name1, name2, name3,strasse,plz,stadt, land, hhupd)
VALUES (source.nachname, source.vorname, source.adr_zusatz,source.str,source.plz,source.ort,source.iso2land, '${hhupd}')
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
推荐阅读
- javascript - 如何在 Laravel 上安装 jsPDF
- c# - 使用 Lambda 表达式生成错误的动态 Linq
- javascript - 有没有办法在外部启用/禁用 SunEditor 上的保存按钮?
- amazon-web-services - 无法为 RDS Aurora postgresql 部署“ProxyTargetGroup”
- javascript - react-router-dom 和 github 页面的问题
- android - 颤振和android gradle错误:任务':app:checkDebugDuplicateClasses'的执行失败
- javascript - 在矩阵中以逐步方式对对象进行排队的算法?
- c - CMake 生成链接器不喜欢的 Makefile
- javascript - 如何使用javascript以特定模式提取矩阵的元素?
- msal - 如何使用 Azure AD B2C 保护 Web API