CODEWITHSUNDEEP
sqlsql-servercount
GreenSoft
GreenSoft

Reputation: 41

How to return count of updated rows in SQL query?

I have a SQL "Update" query and on which I am updating the matching records by Inner JOin of temporary table. Please check the query below-

update tblProduct as p
inner join #temp_Product prod on p.id = prod.id and p.name = prod.name

Now I want to return a table which will contain the columns "id", "name" and the count of updated rows (of tblProduct table) on the basis of "id" and "name".

Please check the tables below-

1) tblProduct

enter image description here

2) #temp_Product

enter image description here

3) And below result should return

enter image description here

Upvotes: 0

Views: 2010

Answers (1)

Mark Adelsberger
Mark Adelsberger

Reputation: 45649

Depending on your database and what you use to access it, you might be able to get a summary count of total updated rows directly from the update statement, but since you want more detail it's unlikely you can use built-in database support.

What I would do is run a separate select query - probably before the update, but in the same transaction if possible - that will count the updates to be applied.

Since you didn't provide the SET expressions I can't include checking that a change would actually be made, but that shouldn't be hard to add to the query (just add where conditions for <oldValue> != <newValue>); the ability to do this is why you'd run the select before the update.

Basic query is:

select p.id, p.name, count(*)
  from            tblProduct as p
       inner join #temp_Product prod 
               on p.id = prod.id 
              and p.name = prod.name
 group by p.id, p.name

Upvotes: 1

Related Questions