can I update all the columns in one set

Question

I am just asking if there is an alternative way for the below update.

update test_table set
col 1 = trim(col1),
col 2 = trim(col2),
col 3 = trim(col3),
col 4 = trim(col4),
col 5 = trim(col5),

In my real table there are about 20 columns, maybe more. Is there a fast way where I can TRIM all the columns in one shot ?
seeking to a similar query if exists:

update test_table set all columns = trim(columns)

Edit:
I can do like this :

UPDATE test_table
SET (col1,col2,col3,col4,col5) = (
SELECT col1,col2,col3,col4,col5
FROM test_table)

Is there other way ?

ChrisProsser ChrisProsser · Accepted Answer · 2013-09-11T12:03:53

There is no SQL syntax to give you a quick and easy way of doing this that I know of.

One way you could do it is to write a PL/SQL block to select the column names of a given table from a system view e.g. user_tab_cols and write them to a variable to build up the SQL code to run dynamically. To be honest, unless you have a number of tables to do this on or loads of columns it would probably be easier to write the query manually.

EDIT - Here is the code incase you want it

declare
   v_table varchar2(50) := 'test_table';
   v_sql varchar2(2000) := null;

   cursor c_cols(p_table varchar2) is
      select c.column_name as col
        from user_tab_cols c
       where c.table_name = upper(p_table)
       order by c.column_id;

begin
   -- write first line of sql...
   v_sql := 'update ' || v_table || ' set' || chr(10);

   -- loop through col names to add each col into update statement...
   for l_c_cols in c_cols(v_table) loop
       v_sql := v_sql || l_c_cols.col || ' = trim(' || l_c_cols.col || '),' || chr(10);
   end loop;

   -- remove last comma...
   v_sql := substr(v_sql,1,length(v_sql)-2);

   -- run dynamic sql...
   dbms_output.put_line(v_sql);
   begin
      execute immediate v_sql;
      commit;
   end;

end;

Let me know if you have any questions on this.

can I update all the columns in one set

1 Answers