子查询返回多个值- SQL

Question

当运行下面的脚本时，我会收到以下错误。任何帮助都将不胜感激。

子查询返回的值超过一个。当子查询跟随=、!=、<、<=、>、>=或子查询用作表达式时，这是不允许的。

脚本

DROP TABLE ProductBase_TEMP

GO

SELECT     *
INTO            ProductBase_TEMP
FROM         PILOT.dbo.PART

GO

UPDATE    ProductBase
SET              CurrentCost =
                          (SELECT     ProductBase_temp.UNIT_LABOR_COST + ProductBase_temp.UNIT_BURDEN_COST + ProductBase_temp.UNIT_SERVICE_COST AS VISUAL_Cost
                            FROM          ProductBase INNER JOIN
                                                   ProductBase_temp ON ProductBase.ProductNumber = ProductBase_TEMP.ID COLLATE Latin1_General_CI_AI)
WHERE     (ProductNumber COLLATE Latin1_General_CI_AI =
                          (SELECT     ProductBase_temp.ID
                            FROM      ProductBase_temp INNER JOIN
                                          ProductBase ON ProductBase_temp.ID COLLATE Latin1_General_CI_AI = ProductBase.ProductNumber))

谢谢

Answer 1

问题是关联子查询中的联接：

UPDATE ProductBase
    SET CurrentCost = (SELECT ProductBase_temp.UNIT_LABOR_COST + ProductBase_temp.UNIT_BURDEN_COST + ProductBase_temp.UNIT_SERVICE_COST AS VISUAL_Cost
                       FROM ProductBase_temp
                       WHERE ProductBase.ProductNumber = ProductBase_TEMP.ID COLLATE Latin1_General_CI_AI
                      )
    WHERE (ProductNumber COLLATE Latin1_General_CI_AI =
              (SELECT ProductBase_temp.ID
               FROM ProductBase_temp 
               WHERE ProductBase_temp.ID COLLATE Latin1_General_CI_AI = ProductBase.ProductNumber
              )
          )

联接是ProductBase表的另一个副本，因此您将得到很多行(完整的结果集)。您希望将查询与外部ProductBase进行关联。