CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2429298
user2429298

Reputation: 13

Subquery returned more than 1 value - SQL

I am receiving the following error when run the script below. Any help would be appreciated.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Script

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

Thanks

Upvotes: 1

Views: 285

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

The problem is the joins in your correlated subqueries:

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

The join is to another copy of the ProductBase table, so you are getting lots and lots of rows (the full result set). You want to correlated the query to the outer ProductBase instead.

Upvotes: 1

Related Questions