Updating mulitple tables

lsalotto · #1 (**permalink**) 11-25-05, 12:57

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

Peter.Vanroose · #2 (**permalink**) 11-25-05, 19:32

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?

sathyaram_s · #3 (**permalink**) 11-28-05, 13:20

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

gardenman · #4 (**permalink**) 11-29-05, 06:06

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;

lsalotto · #5 (**permalink**) 11-29-05, 07:20

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

Internet Services

Web Development

Digital Marketing

Consumer Tech