AW: SQL - Updates with joins

["Hauser, Birgitta" <Birgitta.Hauser@xxxxxxxxxxx>]

Hi Michael,

joined logical files are not updateable, either.

And you never should use a DDS-described logcial file in an SQL-statement.
All SQL-statements where DDS described logical files are specified get
rerouted to the old (classic) Query Engine (CQE).
Not only the advantages of the new (SQL) Query Engine (SQE) cannot be
exploited, this rerouting may cost up to 10-15% performance.

With release V5R4M0 (or even with some PTFs in release V5R3M0) instead of
triggers can be used to update SQL views that join several tables.
Without registering an instead of trigger for the view, an update of the
view will fail. 

In the following article you'll find a nice example:
Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers 
http://www.itjungle.com/fhg/fhg030806-story01.html


Birgitta

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Michael_Schutte@xxxxxxxxxxxx
Gesendet: Montag, 3. April 2006 21:03
An: Midrange Systems Technical Discussion
Betreff: RE: SQL - Updates with joins

I was talking about creating a Logical file but stated view instead.



Michael Schutte
Work 614-492-7419
email  michael_schutte@xxxxxxxxxxxx


                                                                           
             "Elvis Budimlic"                                              
             <ebudimlic@center                                             
             fieldtechnology.c                                          To 
             om>                       "'Midrange Systems Technical        
             Sent by:                  Discussion'"                        
             midrange-l-bounce         <midrange-l@xxxxxxxxxxxx>           
             s@xxxxxxxxxxxx                                             cc 
                                                                           
                                                                   Subject 
             04/03/2006 02:55          RE: SQL - Updates with joins        
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             Midrange Systems                                              
                 Technical                                                 
                Discussion                                                 
             <midrange-l@midra                                             
                 nge.com>                                                  
                                                                           
                                                                           




Joined file SQL view is likely to be read-only, hence it would not
updateable.
John needs to review UPDATE syntax a bit as there is no FROM in the base
UPDATE syntax (there is in subselects, which are often used in UPDATEs for
join purposes).
I'll give it a go for your UPDATE statement, but make sure you test it
first
John:

UPDATE rugd80dat.asbacpp r1
SET bai1tx = 'N', bai2tx = 'N'
WHERE (r1.baartx = 'APV' or r1.baartx = 'MPV' or r1.baartx = 'SPV' or
r1.baartx = 'EPV')
AND r1.bai1tx = ' ' and r1.bai2tx = ' ' AND
EXISTS (SELECT 1 FROM rugd80dat.pmsp0200 r2
WHERE r1.baartx = r2.symbol and r1.baastx = r2.policy0num and r1.baadnb =
r2.module AND r2.trans0stat = 'V' and r2.renewal0cd = '1')

Elvis

-----Original Message-----
Subject: Re: SQL - Updates with joins

I've never seen an update statement with a FROM clause or a JOIN for that
matter.   I suggest creating a view that combines the two files and then
run the update over the new view.

Michael Schutte
Work 614-492-7419
email  michael_schutte@xxxxxxxxxxxx



             "John Candidi"
             <jacandidi@rutger
             sinsurance.com>                                            To
             Sent by:                  <midrange-l@xxxxxxxxxxxx>
             midrange-l-bounce                                          cc
             s@xxxxxxxxxxxx
                                                                   Subject
                                       SQL - Updates with joins
             04/03/2006 02:18
             PM


             Please respond to
             Midrange Systems
                 Technical
                Discussion
             <midrange-l@midra
                 nge.com>






I am having a problem generating the correct SQL script run in iSeries
Navigator.



This is what I have so far:



Update rugd80dat.asbacpp

set bai1tx = 'N', bai2tx = 'N'

from rugd80dat.asbacpp r1,rugd80dat.pmsp0200 r2

where (r1.baartx = r2.symbol and r1.baastx = r2.policy0num and r1.baadnb =
r2.module)

and (r1.baartx = 'APV' or  r1.

baartx = 'MPV' or r1.baartx = 'SPV' or r1.baartx = 'EPV')

and (r1.bai1tx = ' ' and r1.bai2tx = ' ')

and (r2.trans0stat = 'V' and r2.renewal0cd = '1')

When run as a select statement, it returns the correct rows.  When changed
to do the actual update, it reports an unexpected 'from'.  Any
recommendations?  I tried with a 'where exists' with a select subquery, but
it ended up hitting all the rows in the asbacpp table, not just the handful
in the query result.



John A Candidi

Rutgers Insurance Companies

IT Director - AS/400 Manager

856-779-2274



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.