What exactly does "(X row(s) affected)" mean?

Question

I ran a simple query:

UPDATE table

SET user_id = '123456'

WHERE user_id = '234567'

Then I got the error message:

(2942 row(s) affected)
Msg 2627, Level 14, State 1, Line 3
Violation of PRIMARY KEY constraint 'PK__users__6B24EA82'. Cannot insert duplicate key in object 'dbo.users'. The duplicate key value is (123456).
The statement has been terminated.

Does this mean that i have actually made changes to 2942 rows? It doesn't appear that way. I haven't had much luck in my searches. Any help is appreciated.

Answer 1

No, this means you would have made changes to 2942 rows, except one or more of them violates the PRIMARY KEY constraint so they were rolled back.

Answer 2

Assuming that user_id is a primary key (which the error message suggests based on the value in the error message), then the query:

UPDATE table
    SET user_id = '123456'
    WHERE user_id = '234567';

could never affect more than one row in table. user_id is a primary key, so it is unique. The where clause would choose at most one row.

This suggests that there is a trigger on the table. I think the 2,942 is coming from a trigger not from this statement directly. If I had to guess, it is an instead-of update trigger, probably affecting another table.

The error implies that you already have a row in the table where user_id = '123456', so no updates should be made to that table.

(Note: Variations on this idea are possible. The trigger could be updating another table and the primary key violation could be there. The above scenario seems the more likely scenario.)