Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    1

    Unanswered: Subquery returned more than 1 value.

    I keep getting this error message:

    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.


    It seems to be to do with a trigger on tblProduct:

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    ALTER TRIGGER reduce_stock_check
    on tblProduct
    AFTER update
    as
    IF (select InStock from inserted) < 10
    begin
    raiserror ('Number of items in stock cannot be below reorder value of 10 items', 16, 1)
    rollback transaction
    end


    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO



    The problem is that the trigger works correctly and the error message occurs when I try to update tblSupplier:

    UPDATE tblSupplier
    set SupplierID = 35
    where SupplierID = 3


    tblSupplier has a cascade to tblProduct although I can't see how this would generate the error message.



    The annoying thing about all this is that everything worked correctly last week. Nothing had been changed since then.


    Please can people help.

    Hopefully nothing too complicated please as I am a beginner.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    IF (select count(*) from inserted) < 10
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    In your trigger, the table Inserted may contain more than one record.

    Try:
    Code:
    IF (select min(InStock) from Inserted) < 10
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    UPDATE tblSupplier
    set SupplierID = 35
    where SupplierID = 3
    This looks like if you are changing the PK of that record. Is that intentionally? If it is, the cascade will update all tblProduct records that reference this tblSupplier record. So (potentially) multiple records will be updated.
    Last edited by Wim; 04-05-11 at 09:39.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •