2

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?

6
  • 2
    You'll have to do it by modifying your query to exlude rows that don't need updating to be identical. UPDATE t SET family = 'Smith' WHERE name = 'John' and family<>'Smith' ;
    – Philᵀᴹ
    Jan 14, 2016 at 13:36
  • @Phil that may do in the simple example here. But imagine a table with 100 columns where 80 are in the SET part. Your approach is hard to maintain with that. Though it's of course true ;)
    – Izzy
    Jan 14, 2016 at 13:39
  • You can create after update, insert,delete for each row triggers that log the before and after changes to a log table when a column was actually changed. However, depending on the volume of changes in your application you could cause your application to not scale very well. Ultimately preventing this kind of change is a design choice and you need to look at the trade off for performance.
    – Gandolf989
    Jan 14, 2016 at 16:16
  • @Gandolf989 thanks. Yes, that's why this approach is not a good idea with huge amounts of data involved. I was rather looking for a pendant to SQL%ROWNUM – i.e. a variable holding the number of changed records.
    – Izzy
    Jan 14, 2016 at 16:19
  • There is no great way to do that. How much work are you going to do to see if a CLOB or a BLOB has changed? It seems to me that you should just not make this a requirement, since it will likely cause you to add unnecessary complexity to your application.
    – Gandolf989
    Jan 14, 2016 at 16:24

1 Answer 1

4

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.

1
  • Good reasoning, thanks. Not the answer I wanted to hear, though – but I didn't expect an "easy solution" anyway (still, wouldn't have been the first time an SE answer surprised me ;) I'll accept that for now – until the one with the surprise-answer turns up :)
    – Izzy
    Jan 14, 2016 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.