How to update all columns in a table

[Etilman]

Hello, I am trying to write a script that collects a list of all the columns in tables meeting specific criteria (Example 1). Using this information I want to update all the columns selected in each table with a trim(column_name). I have tried using a cursor to do this (Example 2) with no success. Example 2 looks for the literal 'r1.table_name' instead of the value it holds.
Many thanks for your time.

--Example 1:

select column_name, table_name
from user_tab_cols
where table_name like 'DL_%'
;

--Example 2:

declare
cursor c1 is
select column_name, table_name
from user_tab_cols
where table_name like 'DL_%'
;
begin
for r1 in c1 loop
update r1.table_name
set r1.column_name = trim(r1.column_name)
;
end loop;
end;
/

[frozenmist]

Hi,
The update statement has to be dynamic. You cant use variables inside the DML for table name and column name.
Save the query update in a string variable (v_string) as

'UPDATE '||r1.table_name||'SET '|| r1.column_name||' = '||trim(r1.column_name)

Then execute this using
Execute Immediate V_STRING

This should work.Hope this was what you were looking for.
Cheers

[Etilman]

Thats got it! Much obliged for your help. Have a nice day :D