Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: update multiple tables at the same time

    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

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    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;
    /
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •