# Database Discussions > Oracle >  Oracle Sql update Query

## rmadduluri

Hi there,

I have about 20 tables that have a common field called "direction". I want to look for a particular value in thsi "Direction" field and replace it with another value, something like 
Update xxxx
set dir = "W"
where dir = "E"  in all the 20 tables .

Is there a best way to do this instead of writing the query 20 times for each table

Thanks
Rukmini

----------


## skhanal

If your 20 table name have some common pattern, then you can write on statement to spool out 20 update statements and run that.

----------


## rmadduluri

no the tablenames don't have any common name pattern . The only thing they have in common is the fieldname. I'm just wondering if we could write a Pl\Sql script parameterized  for table name

rukmini

----------


## skhanal

set echo off
set termout off
set feedback off
set heading off
set verify off
spool c:\temp\update.sql

select 'update '||owner||'.'||table_name||' set '||column_name||'=''W'' where '||column_name||'=''E'';'
from dba_tab_columns
where column_name='DIR';

spool off
set echo on
set termout off
set feedback on
set heading on
set verify off

@c:\temp\update.sql

----------


## Peter BoydBowmn

Here is a PL/SQL code segment to perform the task specified:

declare
  SQL_STMT  varchar2(2000);
  cursor C1 is  select table_name from all_tab_columns
                 where column_name = 'DIRECTION'
              order by TABLE_NAME;

begin
  for REC in C1 loop
      SQL_STMT := 'update ' || REC.TABLE_NAME || chr(10) || ' set DIRECTION = replace(DIRECTION,'''E''','W''') ' || chr(10) ||
                  ' where DIRECTION = '''E'''  ';
      execute immediate SQL_STMT;
  end loop;

end;
/

----------


## rmadduluri

Thanks guys , I was able to achieve what I needed with "execute immediate"

Rukmini

----------

