CODEWITHSUNDEEP
oracle-databaseoracle11gsql-update
Icaro
Icaro

Reputation: 14845

Update all rows in all tables in Oracle

I have a field (clientID) that repeats in most my tables (what is about 100 or so) and I am trying to update it all at once using oracle 11g. I was able to get the name of the tables but I am not able to use it. From the code below:

BEGIN
   FOR Name IN ( SELECT TABLE_NAME FROM SYS.all_tables where TABLESPACE_NAME='MyTableSpace') LOOP
        DBMS_OUTPUT.PUT_LINE(Name.TABLE_NAME); --I can't see any logs on DBMS Output for some reason
        -- Update Name.TABLE_NAME set ClientID = 1 Where ClientID = 2; --This line does not work message "Table or view does not exist"
   END LOOP;
END;

Upvotes: 3

Views: 2647

Answers (1)

sstan
sstan

Reputation: 36483

You can try using dynamic SQL using EXECUTE IMMEDIATE.

Something like this:

BEGIN
   FOR Name IN ( SELECT TABLE_NAME FROM SYS.all_tables where TABLESPACE_NAME='MyTableSpace') LOOP
        DBMS_OUTPUT.PUT_LINE(Name.TABLE_NAME); --I can't see any logs on DBMS Output for some reason
        EXECUTE IMMEDIATE 'Update ' || Name.TABLE_NAME || ' set ClientID = 1 Where ClientID = 2';
   END LOOP;
END;

If you need to set the values to be updated dynamically, there are ways of binding the parameters with dynamic sql. I just don't remember off-hand how to do it. But if you need that, just ask in the comments, and I'll look for it.

EDIT

I think if you were to bind the values, it would look something like this:

BEGIN
   FOR Name IN ( SELECT TABLE_NAME FROM SYS.all_tables where TABLESPACE_NAME='MyTableSpace') LOOP
        DBMS_OUTPUT.PUT_LINE(Name.TABLE_NAME); --I can't see any logs on DBMS Output for some reason
        EXECUTE IMMEDIATE 'Update ' || Name.TABLE_NAME || ' set ClientID = :1 Where ClientID = :2'
                          USING 1, 2;
   END LOOP;
END;

... where instead of USING 1, 2, you can use variables instead of hardcoding constant values.

For more information: EXECUTE IMMEDIATE.

EDIT 2

If you need to skip tables that don't have the column in question, here is the adjusted query that should do the job:

BEGIN
   FOR Name IN (
     SELECT t.TABLE_NAME 
     FROM all_tables t
     where t.TABLESPACE_NAME='MyTableSpace'
     and exists (
       select null
       from all_tab_columns c
       where c.table_name = t.table_name
       and c.column_name = 'CLIENTID' -- put the right column name here
     )
   ) LOOP
        DBMS_OUTPUT.PUT_LINE(Name.TABLE_NAME); --I can't see any logs on DBMS Output for some reason
        EXECUTE IMMEDIATE 'Update ' || Name.TABLE_NAME || ' set ClientID = 1 Where ClientID = 2';
   END LOOP;
END;

You may have to tweak it slightly, as I don't have an Oracle database in front of me. I may have misspelled a few things.

Upvotes: 5

Related Questions