Oracle TRANSLATE Not Returning Expected Result

Question

We are trying to execute TRANSLATE on a couple hundred rows of data. We have a temporary table containing ID's to rows that need to be updated using TRANSLATE. The odd part is if we attempt to select say two rows, the command is executed properly and the information is updated. However, when we try to run it against 200 rows, it says that 200 rows were updated, but in inspecting the data, they have not been updated. Is there a limitation to the number of rows that can be updated using TRANSLATE in a single query?

We're doing something like:

UPDATE TABLE_A SET COLUMN_TO_UPDATE = 
TRANSLATE(COLUMN_TO_UPDATE,'ñ','n')
WHERE ID IN (SELECT ID FROM TEMP_TABLE);

Answer 1

Phil is right, "No, there isn't a limit", "it /should/ just work", and " this will be difficult to answer without copies of the data or another way to reproduce". Here is a demonstration:

/*
drop table table_a;
drop table temp_table;
create table table_a as 
    (select level id, to_char(level) column_to_update from dual connect by level <=200);
create table temp_table as (select level id from dual connect by level <= 200);
update table_a set column_to_update='2ñ' where column_to_update='2';
*/

SELECT * FROM table_a;

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') 
WHERE ID IN (SELECT ID FROM TEMP_TABLE);

SELECT * FROM table_a;

You could try adding a WHERE clause to verify that TRANSLATE is actually changing rows.

UPDATE table_a SET column_to_update = TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') 
WHERE ID IN (SELECT ID FROM TEMP_TABLE)
AND column_to_update <> TRANSLATE(COLUMN_TO_UPDATE,'ñ','n') ;

If this returns 200 rows then you might not be looking at the correct rows. If it returns zero rows then the TRANSLATE function is not changing the data.