trigger:-Subquery returned more than 1 value. This is not permitted when the subquery

[jideesh]

---------------trigger code
create trigger DeletepurchaseItems on table_purchaseitems
for delete,update
as
begin
select * from deleted
update table_STOCK set ostock=(ostock-(select sqty from deleted))
where itemcode=(select itemcode from deleted)and
coid=(select compid from deleted) and fycode=(select financialyear from deleted)
end
--------------------------------------------------------------
select * FROM TABLE_PURCHASEITEMS WHERE ORDERNO=12030
this statement return 2 records with same orderno

DELETE FROM TABLE_PURCHASEITEMS WHERE ORDERNO=12030
(how can i update table_stock if deleted table contains more than one record by using above delete trigger)
this will return
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.
plz help me what's remedy..

[deepuv04]

---------------trigger code
create trigger DeletepurchaseItems on table_purchaseitems
for delete,update
as
begin
select * from deleted
update table_STOCK set ostock=(ostock-(select sqty from deleted))
where itemcode=(select itemcode from deleted)and
coid=(select compid from deleted) and fycode=(select financialyear from deleted)
end
--------------------------------------------------------------
select * FROM TABLE_PURCHASEITEMS WHERE ORDERNO=12030
this statement return 2 records with same orderno

DELETE FROM TABLE_PURCHASEITEMS WHERE ORDERNO=12030
(how can i update table_stock if deleted table contains more than one record by using above delete trigger)
this will return
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.
plz help me what's remedy..

hi,

in the statement
set ostock=(ostock-(select sqty from deleted)) the select query will return more than one value

and all the select statements specified in the where clause may return more than one value which will give you the error

add a condtion to get a single value in the select statement which will resolve your problem.

I din't get the relation between TABLE_PURCHASEITEMS,table_stock and deleted tables

any way try the following query and make changes according to your requirement, probably will help you

update table_STOCK set ts.ostock = ts.ostock - d.sqty
from table_stock as ts inner join
deleted as d on ts.itemcode = d.itemcode and
ts.coid = d.compid and
ts.fycode = d.financialyear
where (your_conditonS) -- if you have any


thanks

[harmony123]

hi i'm having thesame problem with these:
cmd.CommandText = "UPDATE [tblJan_Abra] SET february=(SELECT A.[february] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;Database=D:\sample_project\SPRS\Book1.xls;HRD= YES','SELECT * FROM [Sheet1$]') AS A) WHERE ID BETWEEN 388 AND 729"
pls help

[OraMaster]

@harmony123
Write your select subquery in such way that it should always return single row or you can use TOP 1 before listing the column name followed by SELECT keyword.
e.g. SELECT TOP 1 ename FROM EMP
It will always return single row.