Subquery returned more than 1 value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery returned more than 1 value

Author

Topic

mody82
Starting Member

13 Posts

Posted - 2009-09-07 : 08:07:33

Hi to all , I have this trigger , when I want to delete a record from the table im getting the below error.

--- This the trigger

ALTER trigger [Cards].[Delete_DamagedCategory1]
On [Cards].[DamagedData]
After Delete
AS
Update Cards.Quantity set Cards.Quantity.Quantity = (Quantity + (select Quantity from Deleted)) where CategoryID in
(Select CategoryID from Deleted ) and AssemblyUnitID in (Select AssemblyUnitID from deleted)
and WareHouseID in ( Select WareHouseID from Deleted) and BranchID=(Select BranchID from Damagedcategory
where DamagedID in ( Select DamagedID from Deleted where ID in (Select ID from Deleted)))

-- This is the error :

((Msg 512, Level 16, State 1, Procedure Delete_DamagedCategory1, Line 5
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.))

So , Im waiting for your Solution

chetan9
Starting Member

3 Posts

Posted - 2009-09-07 : 08:39:59

Hi,
Please find the highlighted part in the query which could be the root cause for the error.

ALTER trigger [Cards].[Delete_DamagedCategory1]
On [Cards].[DamagedData]
After Delete
AS
Update Cards.Quantity set Cards.Quantity.Quantity = (Quantity + (select Quantity from Deleted)) where CategoryID in
(Select CategoryID from Deleted ) and AssemblyUnitID in (Select AssemblyUnitID from deleted)
and WareHouseID in ( Select WareHouseID from Deleted) and BranchID=(Select BranchID from Damagedcategory where DamagedID in ( Select DamagedID from Deleted where ID in (Select ID from Deleted)))

The query that is returning th branch id could be resulting multiple rows. Try using in instead of =

Regards,
Phani Kumar PV

mody82
Starting Member

13 Posts

Posted - 2009-09-07 : 08:53:43

I try your suggestion , but it doesn't solve it
thx for your help

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-07 : 09:11:55

that is pretty ugly. Looks like what you need is this. This assumes that all the columns in my JOIN criteria are your primary key of Cards. If not just use whatever the primary key is to do the JOIN.


update c set
	c.quantity = c.quantity + d.quantity
from	deleted d
inner join Cards c
	on c.id = d.id
	and c.CategoryID = d.CategoryID
	and c.WareHouseID = d.WareHouseID
	and c.DamagedID = d.DamagedID

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-07 : 09:15:52

[code]
UPDATE q
SET Quantity = q.Quantity + d.Quantity
FROM Cards.Quantity q
INNER JOIN deleted d
ON q.CategoryID = d.CategoryID
AND q.AssemblyUnitID = d.AssemblyUnitID
AND q.WareHouseID = d.WareHouseID
INNER JOIN Damagedcategory c
ON d.DamagedID = c.DamagedID
AND q.BranchID = c.BranchID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

mody82
Starting Member

13 Posts

Posted - 2009-09-08 : 07:49:40

Thanks to all ,

I tried your solution dear khtan , It's deleting and working fine , but when it's updating the Quantity in the Main Table , it will take the last record only , assume that I have 4 records in each one there is quantity , the trigger will take the quantity of the last record and updated to Quantity Table.

So, What did you think to do ?
( I hope the case is clear for you )

mody82
Starting Member

13 Posts

Posted - 2009-09-08 : 16:07:13

Thanks Khtan
Actually I was Inserting Duplicate Data , that's why it was not updating the other records.

But when I enter differnet records with different data , it works fine.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-08 : 21:52:29

so what do you want to do for such cases ?

KH
[spoiler]Time is always against us[/spoiler]

mody82
Starting Member

13 Posts

Posted - 2009-09-10 : 06:33:20

I will not allow the user to enter duplicate records , I will put some conditions which will check the inserting data , if it's already inserted so it will update the Quantity field only , else it will accept it as new record.

Best Wishes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources