CODEWITHSUNDEEP
sqlsql-serversql-update
Marek Kwiendacz
Marek Kwiendacz

Reputation: 9834

How to check number of rows changed by last update in SQL Server

I can use @@ROWCOUNT to check number of affected rows by the last UPDATE statement. But it is not number of changed rows in database.

After executing statements like: 

UPDATE User 
SET FirstName = @FirstName 
WHERE Id = @Id

value in @@ROWCOUNT is always 1 - in both situations: when new FirstName is different than old one or not (has been changed or not).

Is there any built-in method to check how many rows has been really changed (not only affected) by last UPDATE?

Upvotes: 0

Views: 111

Answers (2)

M.Ali
M.Ali

Reputation: 69524

  1. Check if you have any Triggers being fired instead of the actual Update statement.

  2. To get the row affected by your update statement you can use OUTPUT clause inside your UPDATE statement to see the rows that were actually updated, by doing something like this.....

    DECLARE @TABLE TABLE (FirstName VARCHAR(100));

 UPDATE [User] 
  SET FirstName = @FirstName 
 OUTPUT Inserted.FirstName INTO @TABLE
 WHERE Id = @Id;

SELECT COUNT(*) FROM @TABLE;

Upvotes: 1

Thomas
Thomas

Reputation: 911

Not really, because mysql isn't checking to see if it needs to do an update, it's just doing it. What you can do is this:

UPDATE User SET FirstName = @FirstName WHERE Id = @Id AND FirstName <> @FirstName

Upvotes: 0

Related Questions