I would like to replace all the cells of a table that match a specific word. I wrote this query:
UPDATE table_name
SET column_name=REPLACE(column_name
,'string_to_be_replaced'
, 'string_replaced')
What will the procedure that will replace the values for all the columns of table_name not only one column as in the code above?
It is something that I will have to do it againg and again to update some tables.
Thanks
Here is some test data:
SQL> select * from t23;
ID NAME JOB
---------- -------------------- --------------------
10 JACK JANITOR
20 JAN TUTOR
30 MOHAN JAZZ DANCER
40 JOAN MECHANIC
SQL>
I want to replace all instances of 'JA' with 'MO'. This means I need to update NAME and JOB. Obviously I could write an UPDATE statement but I can also generate one using the magic of the data dictionary:
SQL> select column_name, data_type
2 from user_tab_cols
3 where table_name = 'T23';
COLUMN_NAME DATA_TYPE
------------------------------ ----------
ID NUMBER
NAME VARCHAR2
JOB VARCHAR2
SQL>
This seems like a one-off task, for which I need an anonymous PL/SQL block rather than a permanent procedure. So here is a script, saved as gen_upd_stmt.sql.
declare
stmt varchar2(32767);
target_string varchar2(20) := 'JA';
replace_string varchar2(20) := 'MO';
begin
stmt := 'update t23 set ';
for lrec in ( select column_name
, row_number() over (order by column_id) as id
from user_tab_cols
where table_name = 'T23'
and data_type = 'VARCHAR2'
)
loop
if lrec.id > 1 then
stmt := stmt || ',';
end if;
stmt := stmt || lrec.column_name || '=replace('
|| lrec.column_name || ', ''' || target_string
|| ''',''' || replace_string
|| ''')';
end loop;
-- uncomment for debugging
-- dbms_output.put_line(stmt);
execute immediate stmt;
dbms_output.put_line('rows updated = '|| to_char(sql%rowcount));
end;
/
Note that generating dynamic SQL is a gnarly process, because syntax errors are thrown at run time rather than compile time. Escaping quotes can be particularly pestilential. It's a good idea to display the generated statement to make debugging easier.
Also, I restricted the targeted columns to those with the correct datatype. This isn't strictly necessary, as replace() will handle type casting for us (in most cases). But it's more efficient with big tables to exclude columns we know won't match.
Anyway, let's roll!
SQL> set serveroutput on
SQL> @gen_upd_stmt
rows updated = 4
PL/SQL procedure successfully completed.
SQL>
As expected all four rows are updated but not all are changed:
SQL> select * from t23;
ID NAME JOB
---------- -------------------- --------------------
10 MOCK MONITOR
20 MON TUTOR
30 MOHAN MOZZ DANCER
40 JOAN MECHANIC
SQL>
For completeness the generated statement was this:
update t23 set NAME=replace(NAME, 'JA','MO'),JOB=replace(JOB, 'JA','MO')
With a larger table or more complicated requirement I would probably introduce line breaks with chr(13)||chr(10) to make the generated code more readable (for debugging).
You can try something like this. Rest update it as per your requirement.
DECLARE
L_statement VARCHAR2(4000) := 'UPDATE :table_name SET ';
CURSOR c_get_cols IS
SELECT column_name
FROM dba_tab_cols
WHERE table_name = :table_name;
TYPE Cur_tab IS TABLE OF c_get_cols%ROWTYPE;
L_tab Cur_tab;
BEGIN
OPEN c_get_cols;
FETCH C_get_cols INTO L_tab;
CLOSE C_get_cols;
FOR i IN 1..L_tab.COUNT
LOOP
L_statement := L_statement || L_tab(i).column_name || ' = REPLACE(column_name, :string_to_be_replaced, :string_replaced)';
IF i != L_tab.COUNT
THEN
L_statement := L_statement || ',';
END IF;
END LOOP;
EXECUTE IMMEDIATE L_statement;
END;
/
I tried it using cursor: (replace owner and table_name with respective values)
DECLARE
COL_NAME ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
string_to_be_replaced VARCHAR2(20) ;
string_replaced VARCHAR2 (20) ;
exc_invalid_id EXCEPTION;
PRAGMA EXCEPTION_INIT(exc_invalid_id, -904);
CURSOR c1 IS
SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE owner=<owner_name> AND TABLE_NAME=<table_name> ;
BEGIN
string_to_be_replaced :='';
string_replaced :='';
OPEN C1;
LOOP
FETCH C1 INTO COL_NAME ;
EXIT WHEN C1%NOTFOUND;
EXECUTE immediate('UPDATE <owner_name>.<table_name> SET '||col_name||'=REPLACE('||col_name||','''||string_to_be_replaced||''','''||string_replaced||''')');
END LOOP;
CLOSE C1;
END;
