CODEWITHSUNDEEP
sql-server
tsohtan
tsohtan

Reputation: 850

Anyone experience update records but only 1 row updated

Is strange! I'm using sql server 2008 and when I perform an update in the query editor, it updates only the first row in that table, and I tried this command SET rowcount 0, but still only one row will get updated.

Anyone faced this before, the syntax is straight forward

UPDATE table
SET    status_one = 0

enter image description here

Upvotes: 1

Views: 53

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

I've seen badly written triggers do (something) like this, for example:

create table User_Profile /* no Tbl, because **why**? */ (
  ID int IDENTITY(1,1) not null,
  tstatus int not null, /* no int, because again, **why** */
)
go
insert into User_Profile (tstatus) values (0)
go 8
create trigger T_User_Profile_U
on User_Profile
instead of update
as
    /* This is a broken trigger, for answering this question

    DO NOT COPY THIS TRIGGER CODE

    if you're trying to write an actual trigger */
declare @ID int
declare @status int
select @ID = ID,@status = tstatus from inserted
    /* The above was broken because despite me knowing to query
    inserted as a table, I've assumed that it contains one row

    whereas, in fact, it may contain 0, 1 or multiple rows.

    I'm not even guaranteed that the @ID and @status values
    will have been retrieved from the same row */

    /* Do important things */

update User_Profile set tstatus = @status where ID = @ID

And now, your query:

set rowcount 0
select tstatus from User_Profile

update User_Profile set tstatus = 1

select tstatus from User_Profile

Results:

tstatus
-------
0
0
0
0
0
0
0
0

and:

tstatus
-------
0
0
0
0
0
0
0
1

Upvotes: 2

Related Questions