需要有关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.

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

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:

当我运行它时，我收到以下错误：

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

错误10/22/2009 1:38:51 PM 0：00：00.000 SQL Server数据库错误：关键字“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);

Now I get the following error:

现在我收到以下错误：

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

错误10/22/2009 2:03:29 PM 0：00：00.000 SQL Server数据库错误：MERGE语句中使用的插入列列表不能包含多部分标识符。请改用单个部件标识符。 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);

I get the error:

我收到错误：

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

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

What am I doing wrong?

我究竟做错了什么？

**** 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);

3 个解决方案

#1

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)

#2

INSERT (SORe.fkey_id, SORe.Color)

should read:

应该读：

INSERT (fkey_id, Color)

Columns in the insert list can only refer to the target table. The parser doesn't expect to see a table alias there, and doesn't know how to resolve it.

插入列表中的列只能引用目标表。解析器不希望在那里看到表别名，也不知道如何解决它。

If it sees "column1", it knows it belongs to the target table. It sees "table1.column1", it doesn't know what "table1" means, since "table1" as a token is out of scope.

如果它看到“column1”，则它知道它属于目标表。它看到“table1.column1”，它不知道“table1”是什么意思，因为“table1”作为一个标记超出了范围。

#3

Try taking out the "AS" in your ") AS SOR"

尝试取出“AS AS”中的“AS”

#1