Update to CD_SETOR column values in all tables found with column

Question

Using the select SELECT * FROM DBA_TAB_COLUMNS WHERE column_name like 'CD_SETOR' i have as a result many tables with different Owner with the same CD_SETOR. EX:

    Owner    Tables                          Column     Type         
210 MVINTEGRA   INTEGRA_ENTRADA_SOLICIT_AGDM CD_SETOR   NUMBER
211 MVINTEGRA   INTEGRA_ENTRADA_SOL_AGDM     CD_SETOR   NUMBER
212 DBAMV   V_FFCV_ESTEIRA_CONTAS            CD_SETOR   NUMBER
213 DBAPS   ITCONTAH_REJEITADA               CD_SETOR   NUMBER
214 DBAPS   ITCONTAH_REJEITADA_0512          CD_SETOR   NUMBER
215 DBAPS   ITCONTA_HOSPITALAR               CD_SETOR   NUMBER
216 DBAPS   ITCONTA_HOSPITALAR_0512          CD_SETOR   NUMBER
217 DBAPS   ITCONTA_MED                      CD_SETOR   NUMBER
218 DBAPS   ITCONTA_MED_0512                 CD_SETOR   NUMBER
219 DBAPS   ITCONTA_MED_REJEITADA            CD_SETOR   NUMBER
220 DBAPS   ITCONTA_MED_REJEITADA_0512       CD_SETOR   NUMBER
221 DBAMV   V_LOTE_ATENDIMENTO               CD_SETOR   NUMBER

Only these tables are populated with codes that no longer exist, and I want to update them all at once to. Ex (Where XPTO codes exist in CD_SETOR column in all tables found, switch to XPT).

Objective: Do not update table by table, there are more than 500 tables with the same FK.

Answer 1

Improving the Robinho’s response

DECLARE
  VN_ALTERADOS NUMBER := 0;
BEGIN
  FOR R IN (select 'update '||owner||'.'||table_name|| ' set '||column_name||'= 406 where '||column_name||' in (20)' LINHA 
            from DBA_TAB_COLUMNS where column_name = 'CD_SETOR') 
  LOOP
    EXECUTE IMMEDIATE (R.LINHA);
    VN_ALTERADOS := VN_ALTERADOS + SQL%ROWCOUNT;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(VN_ALTERADOS);
END;

'setando' the serveroutput on.

Answer 2

If you want to do an UPDATE you need to use "UPDATE" instead of "SELECT", you need to fetch the old values and update them together with WHERE, this might solve your problem: (MAKE A BACKUP OF YOUR DATABASE BEFORE PERFORMING THIS OPERATION)

UPDATE `DBA_TAB_COLUMNS` SET `CD_SETOR`='XPT' WHERE `CD_SETOR`=XPTO;

If you use more than 1 database on the server, don’t forget to choose the database before the operation through the:

use nomedabasededados;

Answer 3

A DBA helped me and the correct script is:

select 'update '||owner||'.'||table_name|| ' set '||column_name||'= 406 where '||column_name||' in (20);'  from DBA_TAB_COLUMNS where column_name = 'CD_SETOR';