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

Question

Once i run this query, getting the following error. please help.

 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

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.

Answer 1

insert a TOP 1 statement into your subquery.

Select TOP 1 Supplier_Item.Price from....

Answer 2

You can use select top 1 to make sure you only have 1 result: (Select top 1 Supplier_Item.Price from Supplier_Item,orderdetails,Supplier where Supplier_Item.SKU=OrderDetails.Sku and Supplier_Item.SupplierId=Supplier.SupplierID )

Also having another good look at the query why it's returning more than 1 value is a good idea, since the cost of a given item should be 1 record. Using explicit join syntax might help for readability.

Answer 3

The select statement in the cost part of your select is returning more than one value. You need to add more where clauses, or use an aggregation.

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

Answer 5

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 6

The fix is to stop using correlated subqueries and use joins instead. Correlated subqueries are essentially cursors as they cause the uery 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, ifyou need both values then the ordinalry 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 inthe 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.

Answer 7

As others have suggested, the best way to do this is to use a join instead of variable assignment. Re-writing your query to use a join (and using the explicit join syntax instead of the implicit join, which was also suggested--and is the best practice), you would get something like this:

select  
  OrderDetails.Sku,
  OrderDetails.mf_item_number,
  OrderDetails.Qty,
  OrderDetails.Price,
  Supplier.SupplierId, 
  Supplier.SupplierName,
  Supplier.DropShipFees, 
  Supplier_Item.Price as cost
from 
  OrderDetails
join Supplier on OrderDetails.Mfr_ID = Supplier.SupplierId
join Group_Master on Group_Master.Sku = OrderDetails.Sku 
join Supplier_Item on 
  Supplier_Item.SKU=OrderDetails.Sku and Supplier_Item.SupplierId=Supplier.SupplierID 
where 
  invoiceid='339740' 

Answer 8

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 9

Either your data is bad, or it's not structured the way you think it is. Possibly both.

To prove/disprove this hypothesis, run this query:

SELECT * from
(
    SELECT count(*) as c, Supplier_Item.SKU
    FROM Supplier_Item
    INNER JOIN orderdetails
        ON Supplier_Item.sku = orderdetails.sku
    INNER JOIN Supplier
        ON Supplier_item.supplierID = Supplier.SupplierID
    GROUP BY Supplier_Item.SKU
) x
WHERE c > 1
ORDER BY c DESC

If this returns just a few rows, then your data is bad. If it returns lots of rows, then your data is not structured the way you think it is. (If it returns zero rows, I'm wrong.)

I'm guessing that you have orders containing the same SKU multiple times (two separate line items, both ordering the same SKU).

Answer 10

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]