update multiple tables at the same time

klada · #1 (**permalink**) 10-15-03, 05:38

Hi!

I'm trying to update multiple tables at the same time. I have plenty of tables in the database which all have some columns in common, one of this tables i would like to change.

I tried something like this (which doesn't work - and a lots of variations which also don't work, but I think helps to understand the problem)

UPDATE
(SELECT table_name FROM user_tables WHERE table_name like 'TD%')
SET my_flag= 1 WHERE depNo=1

Is there any way to solve this problem with SQL (PL/SQL)?

Any help will be appreciated greatly. Thanks.

Klaus

LKBrwn_DBA · #2 (**permalink**) 10-15-03, 14:48

Have you tried dynamic SQL?

Code:


Begin
 For T In (Select Table_Name From User_Tables
            Where Table_Name Like 'TD%')
 Loop
   Execute Immediate 'Update '||T.Table_Name
                   ||'   Set My_Flag= 1 Where Depno=1';
 End Loop;
End;
/

Internet Services

Web Development

Digital Marketing

Consumer Tech