Subquery returned more than 1 value ошибка

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

Basically your query, SP returning multiple rows.

If you are receiving these types of errors without any clue, It is because of an SP, or check your query. There should be a trigger, a function related to a 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 an error but only a 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, there was no clue, 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. Select first

Источник

  • What happens if someone updates the quantity on an existing order?

    The order has to fully procesed through the web application before it triggers into the Stock Management System at this present moment in time.

    That doesn’t answer the question. In programming it is incorrect assumption to say «this does not happen». I don’t know the details of your system, but it is not inconceivable that an order is modified or deleted after the fact. And moreover, your
    trigger should also handle the case that some uses a back door and updates a row in the OrderProductVariant directly through SQL.

    a. what if there are already 20 allocated, and someone adds an order with a Quantity of 4.

    Allocate should be set to 24.

    OK, so we need to modify the trigger to achieve this.

    3.

    The problem that there may be multiple rows inserted with the same value in Sku.

    The Sku remain’s the same on indivdual stocklines in both application. They Link the two.. I just need On insert through the Web to update the specified feilds based on that Sku that never change’s…

    You didn’t get the point. The problem is that

     UPDATE dbo.STOCK
     SET    Allocated = i.Quantity
     FROM   inserted i
     INNER JOIN dbo.STOCK s ON i.Sku = s.[STOCK CODE]

    And there are two rows in inserted for the Sku ABC, one with quantity 18 and one with quantity 41, which should win? Well, from you answer above it follows that the Allocated should be
    incremented with 59. (i.e. not set to 59.)

    It seems that the trigger that I suggested a couple of posts back is actually what you need, but we have to make some modifications:

    ALTER TRIGGER [dbo].[tr_updatestockquantity_productvarient_details_main1]
    ON [dbo].[OrderProductVariant]
    AFTER INSERT, UPDATE, DELETE  AS
    BEGIN
       UPDATE dbo.STOCK
       SET    Allocated = s.Allocated + isnull(i.Quantity, 0) —
                                        isnull(d.Quantity, 0)
       FROM   dbo.STOCK s
       LEFT   JOIN  (SELECT Sku, SUM(Quantity) AS Quantity
                     FROM   inserted
                     GROUP  BY Sku) AS i ON i.Sku = s.[STOCK CODE]
       LEFT    JOIN (SELECT Sku, SUM(Quantity) AS Quantity
                     FROM   deleted
                     GROUP  BY Sku) AS d ON d.Sku = s.[STOCK CODE]
    END

    The trigger now fires for all operations on the OrderProductVariant table, and handles both single-row operations and multi-row operations. It handle changes in Quantity as well as changes in Sku. And don’t tell me that this does happen — I’ve been in this
    game long enough to know that this will happen sooner or later. Important also is that STOCK.Allocated is updated from the current value.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by
      Satheesh Variath
      Sunday, March 3, 2013 1:40 PM
    • Marked as answer by
      RichIEvans
      Friday, March 8, 2013 5:07 PM

    • Источник

    If you get error Msg 512 that reads “Subquery returned more than 1 value…” in SQL Server, it’s because you’re using a subquery that returns more than one value in a scenario where this is not allowed.

    Example of Error

    Suppose we have the following two tables:

    SELECT * FROM Dogs;

SELECT * FROM Cats;

    Result:

    +---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 1       | Fetch     |
| 2       | Fluffy    |
| 3       | Wag       |
| 4       | Fluffy    |
+---------+-----------+

+---------+-----------+
| CatId   | CatName   |
|---------+-----------|
| 1       | Meow      |
| 2       | Fluffy    |
| 3       | Scratch   |
+---------+-----------+

    And we run the following query against those two tables:

    SELECT * FROM Dogs 
WHERE DogName = ( SELECT CatName FROM Cats );

    Result:

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

    We can see that it resulted in error Msg 512.

    This error message explicitly tells us that the “Subquery returned more than 1 value” and that “This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression”.

    The solution to this will depend on what you’re trying to do in the query. Below are a couple of options for resolving this issue.

    Solution 1

    One way to deal with this is to use a different operator. What I mean is, use an operator other than =, !=, <, <= , >, or >=.

    Here’s an example that uses the IN operator:

    SELECT * FROM Dogs 
WHERE DogName IN ( SELECT CatName FROM Cats );

    Result:

    +---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 2       | Fluffy    |
| 4       | Fluffy    |
+---------+-----------+

    Solution 2

    Another option is to keep the equals (=) operator (or whichever operator is in the original query), but change the subquery.

    Here’s an example of changing the subquery, while keeping the equals operator:

    SELECT * FROM Dogs 
WHERE DogName = ( SELECT CatName FROM Cats WHERE CatId = 2 );

    Result:

    +---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 2       | Fluffy    |
| 4       | Fluffy    |
+---------+-----------+

    In this case the subquery returned one value only, and the equals operator was fine with that.

    Solution 3

    Note that the above subqueries only return one column. If the subqueries returned multiple columns, we’d need to change the outer query so that it uses the EXISTS operator.

    Example:

    SELECT * FROM Dogs d
WHERE EXISTS ( SELECT * FROM Cats c WHERE c.CatName = d.DogName );

    Result:

    +---------+-----------+
| DogId   | DogName   |
|---------+-----------|
| 2       | Fluffy    |
| 4       | Fluffy    |
+---------+-----------+

    If we didn’t change it to use the EXISTS operator, then we’d probably get error message 116.

    Источник

    In this guide, we will discuss a common issue encountered while working with subqueries in SQL: the «More than 1 Value» error. We will also provide tips on how to avoid unwanted results when using subqueries.

    Table of Contents

    • Understanding the ‘More than 1 Value’ Error
    • Step-by-Step Guide to Resolve the Error
    • Avoiding Unwanted Results
    • FAQs

    Understanding the ‘More than 1 Value’ Error

    Subqueries are a powerful feature in SQL that allows you to retrieve data from one table based on the data from another table. However, when your subquery returns multiple rows, it can cause issues if the parent query is expecting a single value. This results in the ‘More than 1 Value’ error.

    The error typically occurs when you use a subquery with an operator that expects a single value (such as =, >, <) instead of a set operator (like IN, ANY, ALL).

    Step-by-Step Guide to Resolve the Error

    Step 1: Identify the Error Source

    First, identify the part of your SQL query causing the ‘More than 1 Value’ error. This usually occurs within the WHERE clause when using a subquery.

    Example:

    SELECT *
FROM orders
WHERE order_date = (SELECT order_date FROM orders WHERE customer_id = 1);

    In this example, the subquery returns multiple rows, but the parent query expects a single value to compare with order_date.

    Step 2: Replace the Operator

    Replace the operator causing the issue (=, >, <) with a set operator like IN, ANY, or ALL.

    Example:

    SELECT *
FROM orders
WHERE order_date IN (SELECT order_date FROM orders WHERE customer_id = 1);

    Now the parent query will work with the multiple values returned by the subquery.

    Step 3: Test the Modified Query

    Run the modified query to ensure the ‘More than 1 Value’ error is resolved. You should now receive the desired results without encountering any errors.

    Avoiding Unwanted Results

    When using subqueries, it is essential to avoid unwanted results by ensuring your subquery only returns the data you actually need. Here are some tips:

    Use DISTINCT: To prevent duplicate values in the subquery result, use the DISTINCT keyword.

    SELECT DISTINCT column_name
FROM table_name;

    Limit the Rows: If your subquery returns multiple rows and you only need one, consider using the LIMIT keyword.

    SELECT column_name
FROM table_name
LIMIT 1;

    Add Additional Filtering Conditions: Refine your subquery with additional WHERE conditions to ensure it returns only the desired data.

    SELECT column_name
FROM table_name
WHERE condition1 AND condition2;

    FAQs

    What is a subquery in SQL?

    A subquery (also known as a nested query or inner query) is a SQL query embedded within another query, typically inside a WHERE, HAVING, or SELECT clause. Subqueries help retrieve data from one table based on the data from another table.

    When should I use a subquery?

    Use a subquery when you need to filter or manipulate data from one table based on the values in another table. Subqueries are particularly helpful when dealing with aggregate functions, complex filtering conditions, and retrieving data from multiple tables.

    Can I use multiple subqueries in a single SQL query?

    Yes, you can use multiple subqueries in a single SQL query. However, it is essential to maintain proper syntax and nesting to ensure accurate results and avoid errors.

    How do I refine subquery results to avoid unwanted data?

    To refine subquery results, you can use the DISTINCT keyword to remove duplicates, limit the number of rows returned with the LIMIT keyword, and add additional filtering conditions in the WHERE clause.

    What is the difference between IN, ANY, and ALL set operators in SQL?

    • IN: The IN operator is used to check if a value exists within a set of values returned by a subquery.
    • ANY: The ANY operator is used to compare a value against any value in a set of values returned by a subquery. It is typically used with comparison operators like =, <, >.
    • ALL: The ALL operator is used to compare a value against all values in a set of values returned by a subquery. It is also used with comparison operators like =, <, >.
    • SQL Subquery Tutorial
    • Subqueries in SQL Server
    • SQL Subquery Examples and Best Practices
    Источник
    • Remove From My Forums

    • Вопрос

    • Hello guys,

      I’m having a problem executing a trigger that will fire once an update of multiple rows happens. I get this problem statement:

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

      I’m having a trigger that will send a mail once an update happened to a column. Actually when I update one row (by specifying WHERE clause) the code executes successfully and it send an email.

      I used this:

      Code Snippet


      UPDATE [ISC_Dev].[dbo].[ISC_Workshop] SET [Confirmation] = 1 WHERE [Approval] = 1 AND Register_ID = 3


      But when I update multiple rows (I don’t specify WHERE clause) it give me above error. The result of the query below is more than one row and here comes the issue when trying to update more than one row and at the same time executing the trigger

      I used this to update multiple rows. It is working when the trigger is disabled but when I enable the trigger it gives me an error:

      Code Snippet

      UPDATE [ISC_Dev].[dbo].[ISC_Workshop] SET [Confirmation] = 1 WHERE [Approval] = 1

      I Googled the error and I found that you can not execute a trigger if you are updating multiple rows. I found this article (Multirow Considerations for DML Triggers) http://msdn.microsoft.com/en-us/library/ms190752.aspx and I actullay could’t do it. So if you have an idea of how to implement this method please tell me.

      Now, How can I overcome this issue and find a solution? and is there any other way to do it? if yes, please share!

      I’m including the T-SQL code:

      Code Snippet

      USE [ISC_Dev]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      ALTER TRIGGER [dbo].[SendConfirmationMail]
         ON [dbo].[ISC_Workshop]
         AFTER UPDATE
      AS
      BEGIN
       IF UPDATE([Confirmation])
        IF ((SELECT [MailSent] FROM INSERTED) = ‘False’)
         BEGIN
          SET NOCOUNT ON;
          BEGIN
           — Declare Variables
           DECLARE @_ProfileName NVARCHAR(100)
           DECLARE @_Recipients NVARCHAR(100)
           DECLARE @_CopyRecipient NVARCHAR(100)
           DECLARE @_Body   NVARCHAR(2000)
           DECLARE @_TraineeName NVARCHAR(100)
           DECLARE @_Subject  NVARCHAR(100)
           DECLARE @_RegisterID INT
           DECLARE @_WorkshopID INT
           —Set Variables     
           SET  @_ProfileName = (SELECT [scr_mail].[ml_profile_name] FROM [scr_mail] WHERE [scr_mail].[ml_id] = 1)
           SET  @_Recipients = (SELECT [E_Mail]      FROM INSERTED)
           SET  @_CopyRecipient = (SELECT [Manager_E_Mail]    FROM INSERTED)
           SET  @_Subject  = (SELECT [scr_mail].[ml_subject]  FROM [scr_mail] WHERE [scr_mail].[ml_id] = 1)
           SET  @_RegisterID = (SELECT [Register_ID]     FROM INSERTED)
           SET  @_WorkshopID = (SELECT [Course_ID]     FROM INSERTED)
           — Build Mail Body —
           — Declare Mail Variables 
           DECLARE @MailBody  NVARCHAR(2000)
           DECLARE @Name   NVARCHAR(100)
           DECLARE @WorkshopName NVARCHAR(100)
           DECLARE @WSDate   NVARCHAR(100)
           DECLARE @WorkshopID  INT
           DECLARE @varResult  NVARCHAR(2000)
           — Set Mail Variables 
           SET @MailBody  = (SELECT [ml_body] FROM scr_mail)
           SET @Name   = (SELECT [Prefix] + ‘ ‘ + [First_Name] + ‘ ‘ + [Last_Name] FROM INSERTED)
           SET @WorkshopID  = (SELECT [Course_ID] FROM INSERTED)
           SET @WorkshopName = (SELECT DISTINCT [WorkshopName] + ‘ (‘ + [WorkshopDesc] + ‘) ‘ FROM vw_workshops_details WHERE [WorkshopID] = @WorkshopID)
           SET @WSDate   = (SELECT DISTINCT + ‘[‘ + CONVERT(CHAR(10), [WorkshopStartDate], 103) + ‘] to [‘ + CONVERT(CHAR(10), [WorkshopEndDate], 103) + ‘]’ FROM vw_workshops_details WHERE [WorkshopID] = @WorkshopID)
           — Execute StoredProcedure Passing all paramerters
           EXECUTE spPrepareMail @MailBody,@Name,@WorkshopName,@WSDate,@WorkshopID,
           @varResult OUT
           SET @_Body = @varResult 
           — Update MailSent(T/F) and MailSentDate
           UPDATE [ISC_Workshop]
           SET  [MailSent]  = ‘True’,
             [MailSentDate] = GETDATE()
           WHERE [Register_ID] = @_RegisterID
           — Set NONCOUNT to ‘ON’
           SET NOCOUNT ON
           — Send Mail
           EXEC msdb.dbo.sp_send_dbmail
           @profile_name  = @_ProfileName,
           @recipients   = @_Recipients,
           @copy_recipients = @_CopyRecipient,
           @body    = @_Body,
           @subject   = @_Subject;
          END 
         END
        END

      Thanks in advanced and I hope to find a solution ASAP.

      Regads — Jabri, Himyar

    Источник

    Понравилась статья? Поделить с друзьями:
  • Streamlabs obs ошибка при выводе
  • Stream lesefehler splan 7 ошибка
  • Stray ошибка при запуске игры
  • Strava ошибка подключения к интернету
  • Strava ошибка 403 в ios