CODEWITHSUNDEEP
sqloracle-database
DgBridge
DgBridge

Reputation: 13

Apply Oracle query to list of tables

I have a list of tables in an Oracle instance labeled 'TableA_101818', 'TableB_101818', etc. and I would like to update all of those tables in a single query. I know I can get the list of tables using: 

SELECT table_name FROM all_tables WHERE table_name LIKE 'Table%_101818'

but I'm not sure how to apply something like: 

UPDATE (SELECT table_name FROM all_tables WHERE table_name LIKE 'Table%_101818) 
SET COL1 = 'something' WHERE col2 = 'something else'

Upvotes: 1

Views: 47

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21075

If for all tables the updated strings are identical, you may very easy produce an update script using this SQL.

select   
'UPDATE "'||TABLE_NAME||q'[" SET COL1 = 'something' WHERE col2 = 'something else';]' update_sql
from user_tables where table_name LIKE 'Table%_101818'; 

UPDATE "Table1_101818" SET COL1 = 'something' WHERE col2 = 'something else';
UPDATE "Table2_101818" SET COL1 = 'something' WHERE col2 = 'something else';

Just copy the produced lines in your used SQL tool and run it.

*Note, that in case that your tabele names are in mixed case you must quote the in the generated query.

If you want to check the previos value (before the update), simple add the predicate in the WHEREcondition.

select   
'UPDATE "'||TABLE_NAME||q'[" SET COL1 = 'something' WHERE col2 = 'something else' and COL1 = 'previous value';]' update_sql
from user_tables where table_name LIKE 'Table%_101818';


UPDATE "Table1_101818" SET COL1 = 'something' WHERE col2 = 'something else' and COL1 = 'previous value';
UPDATE "Table2_101818" SET COL1 = 'something' WHERE col2 = 'something else' and COL1 = 'previous value';

In case that there is a diferent previos value in COL1 you will see 0 records updated and no chnage will be done.

The most general case is when the values to be updated differs for each table.

You may set up a helper table containg table name and the values and use it in the query producing the update statements:

TABLE_NAME                     COL1                           COL1_OLD                       COL2                         
------------------------------ ------------------------------ ------------------------------ ------------------------------
Table1_101818                  something                      previous value                 something else                 
Table2_101818                  z                              y                              x

The query joins to the helper table and gets the proper values for each table

select   
'UPDATE "'||a.TABLE_NAME||'" SET COL1 = '''||b.col1||''' WHERE COL2 = '''||
    b.col2||''' and COL1 = '''||b.col1_old||''';' update_sql
from user_tables a
join update_values b on a.table_name = b.table_name
where a.table_name LIKE 'Table%_101818';

UPDATE "Table1_101818" SET COL1 = 'something' WHERE COL2 = 'something else' and COL1 = 'previous value';
UPDATE "Table2_101818" SET COL1 = 'z' WHERE COL2 = 'x' and COL1 = 'y';

Note that this approach works fine for limited number of tables to be processed in a single step. If you have tons of tables or the task is periodical you will want to go the dynamic SQL approach explained in other answer.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

You could use dynamic SQL:

begin
  for r in (SELECT table_name FROM all_tables 
            WHERE table_name LIKE 'Table%_101818') 
  loop
    dbms_output.put_line('SELECT * FROM ' || r.table_name ||
                         q'{ WHERE col2 = 'something else';}');

    execute immediate 'UPDATE ' || r.table_name ||
                      q'{ SET COL1 = 'something' WHERE col2 = 'something else'}';
  end loop;
  commit;
end;
/

Upvotes: 1

Related Questions