kombo
kombo

Reputation: 665

How to update all rows for a column

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

Answers (2)

Radek
Radek

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

Roshan N
Roshan N

Reputation: 1053

Try Something like this:

Update mydatble set ColumnName = <Require value>
where columnname in ('122','566','652')

Upvotes: 0

Related Questions