Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    4

    Unanswered: Updating mulitple tables

    Hi I'm using DB2 V8.1.1.61 FixPack 6 ESE on AIX 5.2
    I having the following query
    Code:
    select
       wp.wwpartnumber,
       cp.country,
       cp.partnumber,
       ft.featurecode,
       ft.fctype,
       cppr.pubfromdate,
       cppr.pubtodate,
       ftdesc.fcmktgname,
       ft.fccat,
       ft.rectype,
       ft.featid,
       cppr.audience,
       cppr.parentid,
       cppr.childid,
       cppr.country,
       ft.process_asload_VV,
       cppr.process_asload_VV
    from
       INT.wwproduct wp,
       INT.cntryprodprodrel cppr,
       INT.cntryproduct cp,
       INT.feature ft,
       INT.country ct,
       INT.featuredesc ftdesc
    WHERE
       wp.wwprodid = cp.wwprodid
       AND fctype = 'Primary FC'
       AND cppr.parentid = cp.prodid
       AND ft.featid = cppr.childid
       AND ft.featid = ftdesc.featid
       AND cp.country = ct.alpha2
       AND ct.lang = substr(ftdesc.language,1,2)
       AND (ft.PROCESS_ASLOAD_VV <> 'P' OR ftdesc.PROCESS_ASLOAD_VV <> 'P' OR cppr.PROCESS_ASLOAD_VV <> 'P')
    I'd like to update the three talbes INT.feature ft,INT.featuredesc ftdesc,INT.cntryprodprodrel cppr, field PROCESS_ASLOAD_VV, but need that only for rows that were selected. I mean something like

    Code:
    UPDATE
      INT.feature ft,INT.featuredesc ftdesc,INT.cntryprodprodrel cppr
    SET
      PROCESS_ASLOAD_VV = 'P',
      UPDATED = CURRENT TIMESTAMP
    WHERE EXISTS 
      (select *
       from
         INT.wwproduct wp,
         INT.cntryproduct cp,
         INT.country ct
       WHERE
         wp.wwprodid = cp.wwprodid
         AND fctype = 'Primary FC'
         AND cppr.parentid = cp.prodid
         AND ft.featid = cppr.childid
         AND ft.featid = ftdesc.featid
         AND cp.country = ct.alpha2
         AND ct.lang = substr(ftdesc.language,1,2)
         AND (ft.PROCESS_ASLOAD_VV <> 'P' OR ftdesc.PROCESS_ASLOAD_VV <>        
         OR cppr.PROCESS_ASLOAD_VV <> 'P') )
    As you see I need to update the three tables at a time but this is imposible in DB2 AFAIK. Am I wrong in that? the thing is that if I split into three different updates when I first update for example INT.FEATURE may leave some records out to update INT.FEATUREDES and INT.CNTRYPRODPRODREL.
    How can this be done? is it posible to update multiple rows as in the query above? I read about it being possible in SQL Server and MySQL but nothing in DB2.
    How about splitting the query into three updates and using transactions? when I commit it won't it happen the same as I stated above?
    I read there is another post like this one but really didn't get a clue
    Thanks for your time...
    Luciano

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Updating a join of 2 or more tables is of course not possible: this is plainly excluded by the relational model since a join typically contains "redundance" which has to be kept identical.
    On the other hand, in your case, updating the three tables separately --one after the other-- should work, or am I misunderstanding your problem?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I agree with Peter Vanroose .. Updating multiple tables is excluded from the relational model ..

    In version 8, DB2 introduced the INSTEAD OF triggers which are actually triggers on views. An update on a view can be 'translated' to multiple update statements in the trigger ..

    Personally, I would recommend using it only in circumstances where using multiple UPDATE Statements is not possible .. For example, third party tools (normally built on a specific J2EE framework eg.Kodo) would see this view as a 'table' and updating this 'table' using multiple UPDATE Statements, though possible is not recommended (as it may defeat the purpose of using the tool/framework for RAD) ..

    HTH

    Sathyaram

  4. #4
    Join Date
    Apr 2004
    Posts
    54
    You can modify more then one table using single SQL-statement.
    You can delete rows from the first table, update second table and insert those rows into therd table.
    Do like this:

    Quote Originally Posted by sathyaram_s

    with TMP1 as (
    select * from old table (UPDATE sometbl1 where ....) as x1
    ), TMP2 as (
    select * from final table (DELETE FROM sometbl2 where
    P1.TMP2=sometbl2.P1...) as x2
    ), TMP3 as (
    select * from final table (insert into XXXX select * from TMP3) x3
    ) select count(*) from TMP3;

  5. #5
    Join Date
    Nov 2005
    Posts
    4
    Thank you guys for all your responses.
    I'll try all your solutions, but actually requirements have changed so I may not need to update all tables at once. Thanks anyway
    Luciano

Posting Permissions

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