Update in the values of the CD_SETOR column in all the tables found with the column

Question

Update in the values of the CD_SETOR column in all the tables found with the column

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

Using select SELECT * FROM DBA_TAB_COLUMNS WHERE column_name like 'CD_SETOR' I get 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 the CD_SETOR column on all found tables, change to XPT).

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

sql sql-update oracle

asked by anonymous 21.06.2017 / 22:37

3 answers

Add + 1d to the current php date How to select Python 3 in Visual code

score 1 · Answer 1

Improving 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;

'setting' the serveroutput on.

score 0 · Answer 2

If you want to do an UPDATE you need to use "UPDATE" instead of "SELECT", you need to fetch old values and update them with WHERE, this might solve your problem: (BACKUP 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, do not forget to choose the database before the operation through:

use nomedabasededados;

score 0 · 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';