需要 Merge 语句方面的帮助 [英] Need help with the Merge statement

问题描述

我想从名为 Sorels 的表更新名为 Sorels_ext 的表.它们之间的联系是Sorels_ext的fkey_id等于Sorels表的identity_column.这是我第一次尝试 Merge 语句，我正在尝试学习语法.

I want to update a table called Sorels_ext from a table called Sorels. The link between them is the fkey_id of Sorels_ext equals the identity_column of the Sorels table. This is my first attempt at a Merge statement and I'm trying to learn the syntax.

MERGE Sorels_ext AS SORe
USING (select SOR.identity_column, CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END from Sorels AS SOR)
ON  (SORe.fkey_id = SOR.identity_column)

WHEN MATCHED THEN
  UPDATE SET SORe.fkey_id = SOR.identity_column, SORe.Color = select SOR.identity_column, CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END
WHEN NOT MATCHED THEN
  INSERT (SORe.fkey_id, SORe.Color) VALUES (SOR.identity_column, SORe.Color = select SOR.identity_column, CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END);

当我运行这个时，我收到以下错误:

When I run this, I get the following error:

错误 10/22/2009 1:38:51 PM 0:00:00.000 SQL Server 数据库错误:关键字ON"附近的语法不正确.46 0

Error 10/22/2009 1:38:51 PM 0:00:00.000 SQL Server Database Error: Incorrect syntax near the keyword 'ON'. 46 0

*** 添加信息 ******

*** ADDED INFO ******

第一次修复后，代码如下:

After the first fix suggested, the code is as follows:

    MERGE Sorels_ext AS SORe
USING (select SOR.identity_column, CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END from Sorels) AS SOR
ON  (SORe.fkey_id = SOR.identity_column)

WHEN MATCHED THEN
  UPDATE SET SORe.fkey_id = SOR.identity_column, SORe.Color = CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END
WHEN NOT MATCHED THEN
  INSERT (SORe.fkey_id, SORe.Color) VALUES (SOR.identity_column, CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END);

现在我收到以下错误:

错误 10/22/2009 2:03:29 PM 0:00:00.000 SQL Server 数据库错误:MERGE 语句中使用的插入列列表不能包含多部分标识符.改用单个部件标识符.55 0

Error 10/22/2009 2:03:29 PM 0:00:00.000 SQL Server Database Error: The insert column list used in the MERGE statement cannot contain multi-part identifiers. Use single part identifiers instead. 55 0

******* 添加了更多信息 *******根据建议进行调整后，我有以下几点:

******* ADDED MORE INFO ****** After adjustments from suggestions, I have the following:

    MERGE Sorels_ext AS SORe
USING (select SOR1.identity_column, CASE
        WHEN left(SOR1.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR1.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END as colors from Sorels as SOR1 ) as SOR 
ON  (SORe.fkey_id = SOR.identity_column)

WHEN MATCHED THEN
  UPDATE SET SORe.fkey_id = SOR.identity_column, SORe.Color = CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END
WHEN NOT MATCHED THEN
  INSERT (fkey_id, Color) VALUES (SOR.identity_column, CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END);

我收到错误:

错误 10/22/2009 2:46:51 PM 0:00:00.000 SQL Server 数据库错误:列名FPARTNO"无效.56 0

Error 10/22/2009 2:46:51 PM 0:00:00.000 SQL Server Database Error: Invalid column name 'FPARTNO'. 56 0

我做错了什么?

**** 我明白了！！！*****

**** I GOT IT!!! *****

MERGE Sorels_ext AS SORe
USING (select SOR.identity_column, CASE
        WHEN left(SOR.FPARTNO, 2) = 'BL' THEN 'Blue'
        WHEN left(SOR.FPARTNO, 2) = 'RD' THEN 'Red'
        ELSE 'White'
      END as colors from Sorels as SOR) SOR1  
ON  (SORe.fkey_id = SOR1.identity_column)

WHEN MATCHED THEN
  UPDATE SET SORe.fkey_id = SOR1.identity_column, SORe.Color = SOR1.colors
WHEN NOT MATCHED THEN
  INSERT (fkey_id, Color) VALUES (SOR1.identity_column, SOR1.colors);

推荐答案

我相信您必须像这样为源数据设置别名:

I believe you have to alias your your source data like so:

USING (select SOR.identity_column, 
   CASE  WHEN left(SOR.FPARTNO, 2) = 'BL' 
   THEN 'Blue'        
   WHEN left(SOR.FPARTNO, 2) = 'RD' 
   THEN 'Red'        
   ELSE 'White'      
   END from Sorels AS SOR) **AS SOR** ON  (SORe.fkey_id = SOR.identity_column)

这篇关于需要 Merge 语句方面的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！