solution for error 512 , subquery return more than 1 value

Question

Post reply

solution for error 512 , subquery return more than 1 value

m_goulay

Old Hand

Points: 374
More actions
September 7, 2009 at 6:12 am

#141352

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sudhanva Ten Centuries Points: 1343 More actions · Answer 1

m_goulay (9/7/2009)
Subquery returned more than 1 value.

and BranchID=(Select BranchID from Damagedcategory where DamagedID in

This is where your query might be returning more than one value.

try changing it something like below:

--to get only one BrandID out of sub-query

and BranchID=(Select top(1) BranchID from Damagedcategory where DamagedID in

(Select DamagedID from Deleted where ID in (Select ID from Deleted)))

--or ; if duplicate BrandID are coming

and BranchID=(Select BranchID from Damagedcategory where DamagedID in

(Select DamagedID from Deleted where ID in (Select ID from Deleted))

group by BranchID )

--or --try changing the logic, adding few more conditions, so that only one BrandID is returned

ps. One Orange Chip Points: 29422 More actions · Answer 2

m_goulay (9/7/2009)
BranchID=(Select BranchID from Damagedcategory
where DamagedID in ( Select DamagedID from Deleted where ID in (Select ID from Deleted)))

BranchID=(Select BranchID from Damagedcategory

This part of the query is the culprit. The subquery is returning more than one result due to which it is failing. Replace the = sign with IN

Pradeep Singh

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Hi

Use a joined UPDATE statement instead of all those sub-queries. It should be faster, more readable and eliminates problems with bulk operations like DELETEs of more than one row.

Greets

Flo

Vijay Mishra SSChasing Mays Points: 647 More actions · Answer 4

Florian is absolutely right, use joins perhaps with CTE this will make your code more readably..

Best Regards,

Vijay Mishra

m_goulay Old Hand Points: 374 More actions · Answer 5

Hi Florian Reischl,

Thx for your suggestion,

if u continue the solution and give me an Example I will be appreciated

best regards

Vijay Mishra SSChasing Mays Points: 647 More actions · Answer 6

not very sure with your table schema, but a query like this is more readable and relatively it would be faster then using sub queries.

update cq set cq.Quantity = quantity + d.quantity

FROM Cards.Quantity cq

JOIN DELETED d

ON d.CategoryID = cq.CategoryID

AND d.AssemblyUnitID = cq.AssemblyUnitID

AND d.WareHouseID = cq.WareHouseID

AND d.DamagedID = cq.DamagedID

JOIN Damagedcategory dc

ON dc.BranchID = cq.BranchID

JOIN DELETED sd

ON sd.ID = dc.DamagedID

Best Regards,

Vijay Mishra

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

You should be very thankful you got the error, because otherwise you would have bad data in your system and not know it. The query needs to be rewritten as joins not for human readability but to give the correct results. :hehe:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

m_goulay Old Hand Points: 374 More actions · Answer 8

Thanks for all who reply in my topic specially Vijay Mishra , Your answer was very useful for me.

and as TheSqlGuru said I should be happy and I'm lucky that I got the error , and with the help of u people I solved.