CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008sql-update
Refael
Refael

Reputation: 7303

TSQL: UPDATE Get num rows updated

I have an update statement like this:

UPDATE TABLE1
SET TABLE1.COL = TABLE2.COL
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.TAB2ID = TABLE2.ID
WHERE TABLE1.COL1 = '123'`

The question of whether there is a way to know how many records were actually updated.

Such as MERGE the OUTPUT $ACTION command.

Upvotes: 0

Views: 6243

Answers (3)

I A Khan
I A Khan

Reputation: 8841

You need to collect the stats as you Want, but @@ROWCOUNT return this:

declare @ABC table (
ColName varchar(32)
)

insert into @ABC values ('A')
insert into @ABC values ('B')
insert into @ABC values ('C')
update @Fish set ColName= 'D' where ColName= 'C'
select @@ROWCOUNT  --Count 1

update @ABC set ColName= 'X'
select @@ROWCOUNT -- Count 3

Here Offical Source

https://technet.microsoft.com/en-us/library/ms187316(v=sql.110).aspx

https://learn.microsoft.com/en-us/sql/t-sql/functions/rowcount-transact-sql

Upvotes: 3

3BK
3BK

Reputation: 1348

You can use @@ROWCOUNT for this.

If the UPDATE is part of a user-defined function, you can RETURN it, like this...

UPDATE
  TABLE1
SET
  TABLE1.COL = TABLE2.COL
FROM
  TABLE1
INNER JOIN
  TABLE2
ON
  TABLE1.TAB2ID = TABLE2.ID
WHERE
  TABLE1.COL1 = '123'
;

RETURN @@ROWCOUNT
;

If it's just a standard UPDATE query, you can use SELECT @@ROWCOUNT immediately after the update to see how many rows were affected.

Upvotes: 2

MarkD
MarkD

Reputation: 5316

To identify the number of rows affected by an operation, you're looking for @@ROWCOUNT

Upvotes: 4

Related Questions