SQL Server Subquery returned more than 1 value This is not permitted when the subquery follows

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         lt    lt           or when the subquery is used as an expression    Any ideas

User · Answer

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

User · Answer

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        lt    lt         else  more than one value   in this case we need to use  in  any  all  some

User · Answer

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

User · Answer

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

User · Answer

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         lt    lt      gt    gt   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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to tsql