Results 1 to 6 of 6
  1. #1
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Unhappy Unanswered: updating multiples tables

    hi,
    a simple question. I have more that 100 table (132 just). one per month from 1997.

    Example:
    H0_CONTABILIDAD_199701
    H0_CONTABILIDAD_199702
    ...
    H0_CONTABILIDAD_200701
    H0_CONTABILIDAD_200702
    ..
    .
    .
    H0_CONTABILIDAD_200712


    Now, how I update all tables with a stantement, sp, etc. Each table have 500k of rows aprox (500.000)

    the idea is:

    update <table>
    set myField = 'newValue'


    TIA

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how do you update all the tables?

    one at a time

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    yep.. one at a time. Every month a ETL full a table.
    But now, I must change a value in all tables... and reprocess all data is imposible (the source is not available).
    Thank!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're welcome

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Provided that the table names are consistant - there seems to be some date logic on the end - couldn't this be done with some dynamic sql and a loop?
    Inspiration Through Fermentation

  6. #6
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    you are right. with a dynamic sql sure.

    this tables are "patitioned table", in db2 I can update all table with a command, but in sql I'am not sure. With the view (select a, b, c from tableA union all ....) the performance is very poor, and create a dynamic sql with a loop is better.
    Thanks!

Posting Permissions

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