0 rows updated while using update query in oracle

Question

When I am trying to update a column with character datatype using update statement. It says 0 rows update and does not throw any error. As the number of columns are much i tried using where clause using specific column out of it...where it updates the row.

Issue is when all the columns are used to alter the value, it gives back 0 rows updated. I have to automate it in vba code/tool so that it could update using all the rows. I identified the columns using which the value is not being updated but there is no error return.

It only says:

0 rows updated.

But what is wrong with that specific column(datatype...decimal).

Please help. I have searched whole internet but no results.

Answer 1

If you are trying to update a specific value in your database and only that one, you may have a rounding issue. This mean the value you see is not the exact value stored.

So you may try to update it with a specific range.

1. First select what you want to update:

   select rowid from table_1 
    where val_x between <x>-<upsilon> and <x>+<upsilon>;
   

2. Update if you have only one value;

   update table_1 set val_x = <new_val>
    where val_x between <x>-0.001 and <x>+0.001
   ;
   

(otherwise, if more than 1 value, decrease <upsilon> to 0.0001, etc.)

Hope it helps