Reputation: 665
I am trying to update multiple rows on one column (SQL Server 2008). The column I need to update has insert and update trigger. When I run this script I got an error message:
UPDATE htable
SET Isverified=1
WHERE columnname IN ('122','566','652')
Error:
Msg 512, Level 16, State 1, Procedure mydatabasename, Line 22
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I don't know how true this is but I want to believe due to the trigger define on this column. Did any want know how I can achieve this
here is the trigger:
ALTER TRIGGER [dbo].[sendTodbase]
ON [dbo].[htable]
AFTER UPDATE
AS
BEGIN DISTRIBUTED TRANSACTION
SET NOCOUNT ON
--CHECK IF DATAENTRY COLUMN IS UPDATED
IF UPDATE(Isverified) begin
declare @dEVer bit declare @rcNum varchar(50)
declare @idenNum varchar(50) declare @docId bigint
--GET INSERTED VALUE AND CHECK IF IT's (YES) THEN CONTINUE...
select @dEVer = (select Isverified from inserted i)
if @dEVer = 1 begin
--END CHECK, IF DE IS COMPLETED CONTINUE----
COMMIT TRAN
Upvotes: 0
Views: 87
Reputation: 1054
Exception is probably generated by:
select @dEVer = (select Isverified from inserted i)
TSQL interpreter expects no more than one row returned by select Isverified from inserted i
subquery.
Your update
query affets more than one row and exception Subquery returned more than 1 value
is generated.
Upvotes: 2
Reputation: 1053
Try Something like this:
Update mydatble set ColumnName = <Require value>
where columnname in ('122','566','652')
Upvotes: 0