CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012sql-update
user3726118
user3726118

Reputation: 73

T-SQL UPDATE statement affects more records than select statement

The following is driving me nuts, please help me!

As a check before running an UPDATE statement (T-SQL; SQL Server 2012) I run: 

select  * 
from Treatment_Day42 
where td42pinit = 'J M' and td42pid = 'ADA';

and I get 1 row returned, as I expect.

However, running 

update Treatment_Day42 set td42pid = 'ADA252' 
where td42pinit = 'J M' and td42pid = 'ADA';

affects four records, not one as in the select statement with the exact same WHERE clause. I get the row returned by the select plus 3 other rows where td42pid = ADA458, ADA671 and ADA658.

Why is the UPDATE affecting more rows than I intend it to?

Thanks.

Clemens

Upvotes: 3

Views: 1136

Answers (4)

Arsman Ahmad
Arsman Ahmad

Reputation: 2221

I was facing the same issue, and then I checked, I had implemented a TRIGGER on myTable.

To check, if you having any trigger on your table or not, just try it:

EXEC sp_helptrigger yourTableName;

Upvotes: 3

Hitesh
Hitesh

Reputation: 3498

You must be having a trigger on your table which must be affecting more rows. You can check for trigger on a table by using 

EXEC sp_helptrigger 'tablename';

For more options on sp_helptrigger, you can visit the below MSDN link

http://msdn.microsoft.com/en-us/library/ms189836.aspx

Upvotes: 0

ZeRaTuL_jF
ZeRaTuL_jF

Reputation: 592

If its not a trigger it could also be on update cascade. This however less common than triggers so I would check triggers first. If not check out your table and the column that you are updating.

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48432

A trigger on the table could do this. Have you checked for an existing trigger?

Upvotes: 4

Related Questions