SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

I run the following query:

SELECT 
   orderdetails.sku,
   orderdetails.mf_item_number,
   orderdetails.qty,
   orderdetails.price,
   supplier.supplierid,
   supplier.suppliername,
   supplier.dropshipfees,
   cost = (SELECT supplier_item.price
           FROM   supplier_item,
                  orderdetails,
                  supplier
           WHERE  supplier_item.sku = orderdetails.sku
                  AND supplier_item.supplierid = supplier.supplierid)
FROM   orderdetails,
       supplier,
       group_master
WHERE  invoiceid = '339740'
       AND orderdetails.mfr_id = supplier.supplierid
       AND group_master.sku = orderdetails.sku

I get the following error:

Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any ideas?

Solution 1:

Try this:

SELECT
    od.Sku,
    od.mf_item_number,
    od.Qty,
    od.Price,
    s.SupplierId,
    s.SupplierName,
    s.DropShipFees,
    si.Price as cost
FROM
    OrderDetails od
    INNER JOIN Supplier s on s.SupplierId = od.Mfr_ID
    INNER JOIN Group_Master gm on gm.Sku = od.Sku
    INNER JOIN Supplier_Item si on si.SKU = od.Sku and si.SupplierId = s.SupplierID
WHERE
    od.invoiceid = '339740'

This will return multiple rows that are identical except for the cost column. Look at the different cost values that are returned and figure out what is causing the different values. Then ask somebody which cost value they want, and add the criteria to the query that will select that cost.

Solution 2:

Check to see if there are any triggers on the table you are trying to execute queries against. They can sometimes throw this error as they are trying to run the update/select/insert trigger that is on the table.

You can modify your query to disable then enable the trigger if the trigger DOES NOT need to be executed for whatever query you are trying to run.

ALTER TABLE your_table DISABLE TRIGGER [the_trigger_name]

UPDATE    your_table
SET     Gender = 'Female'
WHERE     (Gender = 'Male')

ALTER TABLE your_table ENABLE TRIGGER [the_trigger_name]

Solution 3:

SELECT COLUMN 
    FROM TABLE 
WHERE columns_name
    IN ( SELECT COLUMN FROM TABLE WHERE columns_name = 'value');

note: when we are using sub-query we must focus on these points:

if our sub query returns 1 value in this case we need to use (=,!=,<>,<,>....)
else (more than one value), in this case we need to use (in, any, all, some )

Solution 4:

cost = Select Supplier_Item.Price from Supplier_Item,orderdetails,Supplier 
   where Supplier_Item.SKU=OrderDetails.Sku and 
      Supplier_Item.SupplierId=Supplier.SupplierID

This subquery returns multiple values, SQL is complaining because it can't assign multiple values to cost in a single record.

Some ideas:

Fix the data such that the existing subquery returns only 1 record
Fix the subquery such that it only returns one record
Add a top 1 and order by to the subquery (nasty solution that DBAs hate - but it "works")
Use a user defined function to concatenate the results of the subquery into a single string

Solution 5:

The fix is to stop using correlated subqueries and use joins instead. Correlated subqueries are essentially cursors as they cause the query to run row-by-row and should be avoided.

You may need a derived table in the join in order to get the value you want in the field if you want only one record to match, if you need both values then the ordinary join will do that but you will get multiple records for the same id in the results set. If you only want one, you need to decide which one and do that in the code, you could use a top 1 with an order by, you could use max(), you could use min(), etc, depending on what your real requirement for the data is.