合并语法不正确

Question

我有以下脚本，我使用它来更新表中的列，但是，我遗漏了一些东西。有人能帮帮忙吗。基本上，脚本通过匹配名称、地址的子字符串来连接两个表，以找到匹配的表，但实际合并的语法是不起作用的。我可以在合并代码之外毫无问题地运行该脚本，它会向我显示匹配项。问题是当我使用MEGRE代码进行更新时。

Merge Solicitor_Prospects AS T
USING
(
/* ------------------------------------------------ */
select *
  from  (SELECT
          S.[id_Number],
          S.[mrn],
          S.CnBio_First_Name,
          S.CnBio_Last_Name ,
          S.CnAdrAll_1_01_Addrline1,
          S.CnAdrAll_1_01_ZIP,
          S.CnBio_Name,
          S.CnBio_ID,
          S.CnBio_Last_Name+LEFT(S.CnBio_First_Name,3)+MED.DBO.RemoveSpecialChars(LEFT(MED.DBO.Format_address(S.CnAdrAll_1_01_Addrline1,1),5))+left(S.CnAdrAll_1_01_ZIP,5) as DUP_KEY,

          'Address 1 Line 1' As Dup_Source
FROM Solicitor_Prospects AS S
WHERE S.CnAdrAll_1_01_Addrline1 IS NOT NULL) AS p
   join (select s.* from med..LMC_ENTITY s
        ) AS awa on p.CnBio_First_Name = awa.First_Name and p.CnBio_Last_Name = awa.Last_Name and
                 left(p.CnAdrAll_1_01_ZIP,5) = left(awa.Pref_Zip,5) and
                 left(med.[dbo].[fn_remove_char](med.[dbo].[Format_address](p.CnAdrAll_1_01_Addrline1,1)),5) = left(med.[dbo].[fn_remove_char](med.[dbo].[Format_address](awa.Pref_Line1,1)),5)
                 where LEN(p.Dup_Source) > 1
/* ------------------------------------------------ */
)
WHEN MATCHED
       THEN UPDATE SET T.ID_NUMBER = S.IN_NUMBER
GO

Answer 1

试试这个：

Merge Solicitor_Prospects AS T
USING
(
/* ------------------------------------------------ */
select *
  from  (SELECT
          S.[id_Number],
          S.[mrn],
          S.CnBio_First_Name,
          S.CnBio_Last_Name ,
          S.CnAdrAll_1_01_Addrline1,
          S.CnAdrAll_1_01_ZIP,
          S.CnBio_Name,
          S.CnBio_ID,
          S.CnBio_Last_Name+LEFT(S.CnBio_First_Name,3)+MED.DBO.RemoveSpecialChars(LEFT(MED.DBO.Format_address(S.CnAdrAll_1_01_Addrline1,1),5))+left(S.CnAdrAll_1_01_ZIP,5) as DUP_KEY,

          'Address 1 Line 1' As Dup_Source
FROM Solicitor_Prospects AS S
WHERE S.CnAdrAll_1_01_Addrline1 IS NOT NULL) AS p
   join (select s.* from med..LMC_ENTITY s
        ) AS awa on p.CnBio_First_Name = awa.First_Name and p.CnBio_Last_Name = awa.Last_Name and
                 left(p.CnAdrAll_1_01_ZIP,5) = left(awa.Pref_Zip,5) and
                 left(med.[dbo].[fn_remove_char](med.[dbo].[Format_address](p.CnAdrAll_1_01_Addrline1,1)),5) = left(med.[dbo].[fn_remove_char](med.[dbo].[Format_address](awa.Pref_Line1,1)),5)
                 where LEN(p.Dup_Source) > 1
/* ------------------------------------------------ */
) S on S.ID_NUMBER = T.ID_NUMBER -- !!! You forgot alias and condition. I took ID_NUMBER, but I think you should write here other columns (which should be used for to link target and source sets) !!!
WHEN MATCHED
       THEN UPDATE SET T.ID_NUMBER = S.IN_NUMBER
GO