Oracle: update multiple columns with dynamic query

Question

I am trying to update all the columns of type NVARCHAR2 to some random string in my database. I iterated through all the columns in the database of type nvarchar2 and executed an update statement for each column.

for i in (
    select 
        table_name,
        column_name 
    from 
        user_tab_columns
    where 
        data_type = 'NVARCHAR2'
    ) loop
execute immediate 
    'update ' || i.table_name || 'set ' || i.column_name ||
    ' = DBMS_RANDOM.STRING(''X'', length('|| i.column_name ||'))
    where ' || i.column_name || ' is not null';

Instead of running an update statement for every column of type nvarchar2, I want to update all the nvarchar columns of a particular table with a single update statement for efficiency(that is, one update statement per 1 table). For this, I tried to bulk collect all the nvarchar columns in a table, into a temporary storage. But, I am stuck at writing a dynamic update statement for this. Could you please help me with this? Thanks in advance!

have an inner loop for all the columns of a specific table, and append columns to update. Is this something you have to run repeatedly, or one time? If one time, just write a script that emits another script with the updates, then run the second script. — OldProgrammer
– OldProgrammer, Commented Dec 2, 2016 at 16:25
Appending the columns to 'SET' is where I got stuck. It is not possible to write a select inside the set. Do you have any idea on how to append? — rav
– rav, Commented Dec 2, 2016 at 16:30
Create the update with a string, append to that, then execute immediate on the string. — OldProgrammer
– OldProgrammer, Commented Dec 2, 2016 at 16:39
How you are going to update only rows with not null in multicolumn update mode? — Konstantin Sorokin
– Konstantin Sorokin, Commented Dec 2, 2016 at 16:54
@KonstantinSorokin you won't add an where, instead you update every column with NVL or COALESCE :) — Jorge Campos
– Jorge Campos, Commented Dec 2, 2016 at 16:57

Wernfried Domscheit · Accepted Answer · 2016-12-02 20:18:03Z

2

You can try this one. However, depending on your table it could be not the fastest solution.

for aTable in (
    select table_name,
        listagg(column_name||' = nvl2('||column_name||', DBMS_RANDOM.STRING(''XX'', length('||column_name||')), NULL)') WITHIN GROUP (ORDER BY column_name) as upd,
        listagg(column_name) WITHIN GROUP (ORDER BY column_name) as con
    from user_tab_columns 
    where DATA_TYPE = 'NVARCHAR2'
    group by table_name
  ) loop

    execute immediate 
       'UPDATE '||aTable.table_name ||
       ' SET '||aTable.upd ||
       ' WHERE COALESCE('||aTable.con||') IS NOT NULL';

end loop;

Resulting UPDATE (verify with DBMS_OUTPUT.PUT_LINE(..)) should look like this:

UPDATE MY_TABLE SET 
   COL_A = nvl2(COL_A, DBMS_RANDOM.STRING('XX', length(COL_A)), NULL),
   COL_B = nvl2(COL_B, DBMS_RANDOM.STRING('XX', length(COL_B)), NULL)
WHERE COALESCE(COL_A, COL_B) IS NOT NULL;

edited Dec 2, 2016 at 20:18

answered Dec 2, 2016 at 19:19

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gurwinder Singh · Accepted Answer · 2016-12-02 17:17:40Z

1

Please try this:

DECLARE
    CURSOR CUR IS
        SELECT
            TABLE_NAME, 
            LISTAGG(COLUMN_NAME||' = DBMS_RANDOM.STRING(''X'', length(NVL('||
            COLUMN_NAME ||',''A''))',', ')
            WITHIN GROUP (ORDER BY COLUMN_ID) COLUMN_NAME
        FROM DBA_TAB_COLUMNS 
        WHERE DATA_TYPE = 'NVARCHAR2'
        GROUP BY TABLE_NAME;
    TYPE TAB IS TABLE OF CUR%ROWTYPE INDEX BY PLS_INTEGER;
    T TAB;
    S VARCHAR2(4000);
BEGIN
    OPEN CUR;
    LOOP
        FETCH CUR BULK COLLECT INTO T LIMIT 1000;
        EXIT WHEN T.COUNT = 0;
        FOR i IN 1..T.COUNT LOOP
            S := 'UPDATE ' || T(i).TABLE_NAME || ' SET ' || T(i).COLUMN_NAME;
            EXECUTE IMMEDIATE S;
        END LOOP;
    END LOOP;
    COMMIT;
END;
/

edited Dec 2, 2016 at 17:17

answered Dec 2, 2016 at 17:02

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

5 Comments

Gurwinder Singh Over a year ago

@rav the NVL part is to take care of the null values in multi-column update. If it's not suitable for you, then you have to do it one by one. no other go.

rav Over a year ago

Thanks for the effort. Appreciated!

Konstantin Sorokin Over a year ago

''A''))' -> ''A'')))'. Add analogue of where colname is not null filter, please.

Gurwinder Singh Over a year ago

@Konstantin I already submitted that it cant be done. See my comment above.

Konstantin Sorokin Over a year ago

how about col = nvl2(col, dbms_random.string('x', nvl(length(col, 0)), null)?

Sentinel · Accepted Answer · 2016-12-02 19:20:30Z

1

I think that would do it. But as I said in the comments, you need to validate the syntax since I don't have an Oracle instance to test it.

for i in (
    select table_name,
           'update || i.table_name || set ' || 
             listagg( column_name || '= NLV( ' || column_name || ', ' 
               || 'DBMS_RANDOM.STRING(''X'', length('|| column_name ||') ) )'
               || ';'
             ) WITHIN GROUP (ORDER BY column_name) as updCommand
    from user_tab_columns 
    where DATA_TYPE = 'NVARCHAR2'
    group by table_name
  ) loop

    execute immediate i.updCommand;

end loop;

If you find any error, let me know in the comments so I can fix it.

edited Dec 2, 2016 at 19:20

Sentinel

6,4871 gold badge20 silver badges24 bronze badges

answered Dec 2, 2016 at 17:39

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

5 Comments

Konstantin Sorokin Over a year ago

within group is absent

rav Over a year ago

Error report - SQL Error: ORA-02000: missing WITHIN keyword 02000. 00000 - "missing %s keyword"

Jorge Campos Over a year ago

@rav Fixed it. Try now please.

rav Over a year ago

Error at Command Line : 11 Column : 3 Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause:*Action: Error starting at line : 15 in command - end loop Error report - Unknown Command

Sentinel Over a year ago

The OP wanted to update columns that contained data, so the NVL function is not necessary, though NVL2 would avoid errors when the column is null. Also to avoid unnecessary updates where all columns are null, use listagg a second time to build the where clause: ' WHERE '|| LISTAGG(column_name||' is not null', ' OR ') within group (order by column_id)

Collectives™ on Stack Overflow

Oracle: update multiple columns with dynamic query

3 Answers 3

Comments

5 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related