Oracle – How to Find the Number of Rows Changed by an UPDATE Statement

oracle

Imagine the following table:

id | name | family
---+------+--------
 0 | John | Smith
 1 | Mary | Winters
 2 | John | Doe

and now the following statement:

UPDATE t SET name = 'John' WHERE id = 0;

This statement in fact would update nothing – as it reflects what already is there. Now all the blogs, manuals and whatsnots said to check SQL%ROWCOUNT for the number of updated rows. Doing that returns 1 (affected rows – i.e. those the WHERE clause did match). Let's have another one:

UPDATE t SET family = 'Smith' WHERE name = 'John';

De facto, that would only change the row with id=2, so I want something stating "1 row updated" – but SQL%ROWCOUNT gives us 2 (again, rows affected).

So here comes the final question: What gives the number of rows changed?

Best Answer

If you issue an update statement on your table that effectively updates the data to the same value that is already there, then the database does update the row, so the SQL%ROWCOUNT is accurate even though the new value is the same as the old value.

You can use a flashback versions query to see that database has indeed updated the record:

select t.*
     , VERSIONS_OPERATION
     , VERSIONS_STARTTIME
     , VERSIONS_ENDTIME
     , VERSIONS_XID
  from t
  versions between timestamp systimestamp - interval '10' minute and systimestamp
 where id = 0;

UPDATE t SET name = 'John' WHERE id = 0;
commit;

select t.*
     , VERSIONS_OPERATION
     , VERSIONS_STARTTIME
     , VERSIONS_ENDTIME
     , VERSIONS_XID
  from t
  versions between timestamp systimestamp - interval '10' minute and systimestamp
 where id = 0;

In the first query above there will likely be only one row returned for ID=0. Then after issuing and committing an update against ID=0 the second time the flashback versions query is run you should get back two records, one for the original record where the VERSIONS_ENDTIME column has been filled in and one for the new version of the record showing that the operation against it was an Update and that the record has a VERSIONS_STARTTIME equal to the end time of the original record.

The only way to prevent records from being updated with the same values is either with an appropriate predicate in the where clause, or in an on update for each row trigger that rejects the change by raising an error.

Related Question