x

Subquery return more than 1 value, Msg 512

I have Two Tables: Quantity Table & Purchase Table. When I insert data into Purchases Table the trigger should fire and update the ActualQuantity in the Quantity Table if the condition is satisfied.

This means this value has been inserted in the Quantity table before and now we will only update the ActualQuantity field only, else if this condition are not satisfied means there are different values, at that time u can't Update the ActualQuantity Field because it hasn't been inserted before, so you have to Insert this values in Quantity Table.

The problem is when I Insert 1 record it will accept, but If I insert more than one records in one Insert statement it will give me this error.

Insert Into Purchases.BInvData
  (BuyInvoiceID,CategoryID,AssemblyUnitID,WareHouseID,Quantity,ExpireDate)
Select 1,1001,1,1,100,'1/1/2009'
union all
Select 1,1002,1,1,500,'22/12/2009'
Union all
Select 3,1003,1,1,700,'22/12/2009'

Msg 512 : Subquery returm more than 1 value ...............

Please check the trigger and inform me where is the error ?

---------------------------- **My Trigger** ------------------------

ALTER Trigger [Purchases].[BInvQty] on [Purchases].[BInvData] for Insert
AS
DECLARE @rc INT, @WareHouseID int, @AvailableQuantity int,
  @InventoryQuantity int , @ExpireDate nVarchar(50), @CategoryID int,
  @AssemblyUnitID int,@BranchID int, @CategoryMovement int,
  @DamagedCategory int,@BuyInvoiceID int
BEGIN TRAN
UPDATE Q
SET AvailableQuantity = Q.AvailableQuantity + I.Quantity
FROM Cards.Quantity Q
INNER JOIN Inserted I   	
  ON Q.CategoryID = I.CategoryID
    AND Q.AssemblyUnitID = I.AssemblyUnitID
    AND Q.WareHouseID = I.WareHouseID
    AND Q.ExpireDate = I.ExpireDate
INNER Join BuyInvoice Bi
  ON Q.BranchID = Bi.BranchID
INNER Join Inserted It
  ON It.BuyInvoiceID = I.BuyInvoiceID

Set @rc=@@RowCount
IF @rc=0
BEGIN 
set @WarehouseID = (Select WareHouseID from Inserted)
Set @AvailableQuantity = (Select Quantity from Inserted)
Set @ExpireDate = (Select ExpireDate from Inserted)
Set @CategoryID = (Select CategoryID from Inserted)
Set @AssemblyUnitID = (Select AssemblyUnitID from Inserted)
Set @BranchID = (Select BranchID from Purchases.BuyInvoice where BuyInvoiceID in
  (Select BuyInvoiceID from Inserted))
Set @CategoryMovement = (Select (Quantity - (Select Quantity From Inserted)) as
  Quantity from Purchases.BInvdata where ID in (Select ID from Inserted))

Insert into Cards.Quantity Values
  (@WareHouseID,@AvailableQuantity,0,@ExpireDate,@CategoryID,
  @AssemblyUnitID,@BranchID,@CategoryMovement,0)

END
COMMIT
--------------------------------

I Hope it's clear for you

more ▼

asked Oct 27, 2009 at 07:02 AM in Default

Mohamed gravatar image

Mohamed
34 2 3 3

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Also if you rewrote the last bit without the local variables, it might be more clearer that you could be inserting dupe rows (I don't know whether you meant to do that or not....)

Insert into Cards.Quantity 
select
    I.WareHouseID, 
    I.Quantity, 
    0, 
    I.ExpireDate, 
    I.CategoryID, 
    I.AssemblyUnitID
    BuyInvoice.BranchID,
    BInvdata.Quantity - I.Quantity,
    0
from INSERTED I
join Purchases.BuyInvoice BuyInvoice on PB.BuyInvoiceID = I.BuyInvoiceID
join Purchases.BInvdata BInvdata on BInvdata.ID = I.ID

The joins could be duping up your base data

more ▼

answered Oct 27, 2009 at 07:55 AM

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

(comments are locked)
10|1200 characters needed characters left

I like both Rob's and Kev's answer and have voted them up, but one thing they haven't actually said is that the inserted and deleted tables can contain multiple rows. You don't have your trigger fire once for every row that is inserted/updated/deleted - triggers fire once per operation and will represent all the rows in that operation.

So you need to be using something like Kev's query, although it's likely that you might want to be doing some GROUP BY and aggregation operations in order to insert the minimum number of rows that you want.

Another thing I'd like to point out which is present in your original code (and, by extension in Kev's code), is that this code

Insert into Cards.Quantity 
select ...

... is a time-bomb waiting to go off. If somebody adds a column to the table Cards.Quantity, or the columns get re-ordered in the future, or if somebody removes a column, then your INSERT operation will then fail. Now, while you know the columns it is obvious to you what is happening, but - in the future - you will have to try and remember what columns you were trying to INSERT into in order to fix it.

more ▼

answered Oct 27, 2009 at 08:20 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Yes, I figured that the trigger behaviour was a given. Probably the key missing info for the asker though. +1
Oct 27, 2009 at 08:27 AM Rob Farley
There's a couple of seeder questions right there :)
Oct 27, 2009 at 08:47 AM Matt Whitfield ♦♦
Thanks all for your Solution , It has been solved , But still there's small error , incase im Inserting 3 records , one of them is inserted earlier , at that time it will Insert the other two including the one which inserted earlier even after updated. what shall I Do ?
Oct 27, 2009 at 09:21 AM Mohamed
Sorry I'm not clear on what you mean - can you maybe extend your question above to describe it in a bit more detail?
Oct 27, 2009 at 09:30 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

The problem is with the assignment queries down the end.

What do you want to be put into Cards.Quantity when there are multiple rows inserted?

Try turning that INSERT statement into one that uses SELECT instead of VALUES.

more ▼

answered Oct 27, 2009 at 07:23 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

Dear Matt Whitfield , first Thanks for your help , I mean that if I Insert more than record in one insert statement into purchases table, the trigger will fire , then it will check the records, if it's there or not , then it will update the available records and it will go to the next stage that's Insert Statement (as u seen the trigger ) , the problem is after inserting the records which not available in the quantity table it will insert also the recently updated record which it's already inserted earlier and got update

Hope it's clear

more ▼

answered Oct 27, 2009 at 10:56 AM

Mohamed gravatar image

Mohamed
34 2 3 3

Ok, in that case you want something like INSERT INTO destination (column1, column2) SELECT column1, column2 FROM inserted LEFT OUTER JOIN destination ON destination.key = inserted.key WHERE destination.key IS NULL
Oct 27, 2009 at 11:06 AM Matt Whitfield ♦♦
Dear Matt Whitfield I'm not able to get your answer , still I'm facing the problem , what do u mean by destination.Key , you mean the PrimaryKey More Explain Plz
Oct 27, 2009 at 11:51 AM Mohamed
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x117
x46

asked: Oct 27, 2009 at 07:02 AM

Seen: 3132 times

Last Updated: Oct 27, 2009 at 07:27 AM