[sql] 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?

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' 

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

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.

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

I had the same problem , I used in instead of = , from the Northwind database example :

Query is : Find the Companies that placed orders in 1997

Try this :

SELECT CompanyName
    FROM Customers
WHERE CustomerID IN (
                        SELECT CustomerID 
                            FROM Orders 
                        WHERE YEAR(OrderDate) = '1997'
                    );

Instead of that :

SELECT CompanyName
    FROM Customers
WHERE CustomerID =
(
    SELECT CustomerID 
        FROM Orders 
    WHERE YEAR(OrderDate) = '1997'
);

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:

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

Even after 9 years of the original post, this helped me.

If you are receiving these types of errors without any clue, there should be a trigger, function related to the table, and obviously it should end up with an SP, or function with selecting/filtering data NOT USING Primary Unique column. If you are searching/filtering using the Primary Unique column there won't be any multiple results. Especially when you are assigning value for a declared variable. The SP never gives you en error but only an runtime error.

 "System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated."

In my case obviously there was no clue, but only this error message. There was a trigger connected to the table and the table updating by the trigger also had another trigger likewise it ended up with two triggers and in the end with an SP. The SP was having a select clause which was resulting in multiple rows.

SET @Variable1 =(
        SELECT column_gonna_asign
        FROM dbo.your_db
        WHERE Non_primary_non_unique_key= @Variable2

If this returns multiple rows, you are in trouble.

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.

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.

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]