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

    Unanswered: How to update multiple tables with sql

    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 (Oracle 8.1)?

    Any help will be appreciated greatly. Thanks.

    Klaus

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You cant update multiple tables with one update statement. What you can do though is get Oracle to write your multiple update statments for you i.e.

    select 'update '||table_name||' set col=X where col=Y;'
    from user_tab_columns
    where column_name = 'COL'

    and then paste it into sqlplus or something similar.

    Alan

  3. #3
    Join Date
    Oct 2003
    Posts
    3
    Hi Alan,

    thanks a lot. This hint helped to do the job!

    Klaus


    Originally posted by AlanP
    You cant update multiple tables with one update statement. What you can do though is get Oracle to write your multiple update statments for you i.e.

    select 'update '||table_name||' set col=X where col=Y;'
    from user_tab_columns
    where column_name = 'COL'

    and then paste it into sqlplus or something similar.

    Alan

Posting Permissions

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