SQL Server Subquery returned more than 1 value. Th

Question

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?

Answer 1

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]

Answer 2

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.

Answer 3

The error implies that this subquery is returning more than 1 row:

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

You probably don't want to include the orderdetails and supplier tables in the subquery, because you want to reference the values selected from those tables in the outer query. So I think you want the subquery to be simply:

(Select Supplier_Item.Price from Supplier_Item where Supplier_Item.SKU=OrderDetails.Sku and Supplier_Item.SupplierId=Supplier.SupplierID )

I suggest you read up on correlated vs. non-correlated subqueries.

Answer 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:

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