RE: SQL - Updates with joins

["John Candidi" <jacandidi@xxxxxxxxxxxxxxxxxxxx>]

Thank you all. The solution worked just fine.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Ryan Hunt
Sent: Monday, April 03, 2006 2:34 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL - Updates with joins

Jon, don't worry about it.  Unless you are on an old version of OS/400,
"EXISTS(SELECT 1" and EXISTS(SELECT*" should give you the same results and
the same access plan.

SELECT 1 is an old optimizer trick DBA's use to limit the amount of data
returned to the satisfy the exists clause (thus limiting memory and I/O
usage).  However, nowadays, I find most RDBMS's properly identify EXISTS
clauses and perform just enough I/O to satisfy the  equality comparisons
only.

Ryan


"John Candidi" <jacandidi@xxxxxxxxxxxxxxxxxxxx>
wrote in message news:00b601c65753$bc6dc390$560a0a0a@xxxxxxxxxxxxxxxxxxxxxxx
> Elvis, what does this 1 represent?
>
> EXISTS (SELECT 1
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On
Behalf Of Elvis Budimlic
> Sent: Monday, April 03, 2006 1:56 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: SQL - Updates with joins
>
> 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.
>
> -- 
> 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.
>
>