Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);
share|improve this question
    
No, there isn't a limit. Have you inspected the data in a different session without COMMITting in the UPDATE session? (Common mistake, may as well ask). This won't really be possible to answer without test data, as no error message is returned and it /should/ just work. There is definitely something else in play here.... –  Phil Nov 30 '12 at 14:58
    
Thanks Phil. The DBA has been inspecting it in the same session without committing. –  John F. Nov 30 '12 at 15:07
1  
You do realise that TRANSLATE() replaces characters with one another & not whole strings? If you want to replace whole strings, use REGEXP_REPLACE(). By the way, just because it says 200 rows updated doesn't mean that the TRANSLATE() function actually did anything. COLUMN_TO_UPDATE may have merely been updated to be the same as the previous value. –  Phil Nov 30 '12 at 15:12
    
Sorry, I just included some random 'blahs' in my example. We are in fact replacing specific characters in our query. –  John F. Nov 30 '12 at 15:22
    
In that case, this will be difficult to answer without copies of the data or another way to reproduce. –  Phil Nov 30 '12 at 15:26

1 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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