References: <472789db@newsgate.x-privat.org>
In-Reply-To: <472789db@newsgate.x-privat.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: rchp4.rochester.ibm.com
Message-ID: <4727af53$1@kcnews01>
X-Trace: kcnews01 1193783123 129.42.161.36 (30 Oct 2007 22:25:23 +0100)
Organization: Global Network Services - Remote Access Mail & News Services
Lines: 87
X-Complaints-To: abuse@prserv.net
Path: border1.nntp.dca.giganews.com!nntp.giganews.com!wn 11feed!worldnet.att.net!12.154.55.82!kcnewsm02.prs erv.net!prserv.net!kcnews01!not-for-mail
Bytes: 4014
Xref: number1.nntp.dca.giganews.com comp.sys.ibm.as400.misc:214655

I have not had access to an AS/400 for years, so I can not test on
any old releases. I would have to check the documentation for any
specific AS/400 release to determine if/what was supported.

Using DB2 for i5/OS on a System i, if a SQL DELETE with a WHERE
EXISTS is easy for a one-to-one relationship, then so too for a SQL
UPDATE with a WHERE EXISTS. Given that the selection in the WHERE
EXISTS from the DELETE statement is represented by <...>, then
essentially just repeat that same selection in the UPDATE statement for
both the values and WHERE EXIST, as in the following SQL DELETE & SQL
UPDATE statements:

DELETE FROM F1
WHERE EXISTS (SELECT '1' FROM F2 <...> )

UPDATE F1 SET (P, Q) =
(SELECT R, S FROM F2 <...>
WHERE EXISTS (SELECT '1' FROM F2 <...> )

So with the given scenario, if the following DELETE works:

delete from F1
where exists (select 1 from F2
where F1.A=F2.C and F1.B=F2.D
and (F1.P<>F2.R or F1.Q<>F2.S) )

Then the following update should suffice [if indeed a one-to-one
relationship] in that scenario:

update F1
set (P, Q) = (select R, S from F2
where F1.A=F2.C and F1.B=F2.D
and (F1.P<>F2.R or F1.Q<>F2.S) )
where exists (select 1 from F2
where F1.A=F2.C and F1.B=F2.D
and (F1.P<>F2.R or F1.Q<>F2.S) )

The above statement could be reduced to the following, due to the
one-to-one relationship:

update F1
set (P, Q) = (select R, S from F2
where F1.A=F2.C and F1.B=F2.D )
where exists (select 1 from F2
where F1.A=F2.C and F1.B=F2.D
and (F1.P<>F2.R or F1.Q<>F2.S) )

I prefer to code explicitly to eliminate the OR:

update F1
set (P, Q) = (select R, S from F2
where F1.A=F2.C and F1.B=F2.D )
where exists (select 1 from F2
where F1.A=F2.C and F1.B=F2.D
and not (F1.P=F2.R and F1.Q=F2.S) )

Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer

Richard wrote:
> Thought I'd pose this problem here, as it seems rather problematical
> in AS/400 SQL
>
> Scenario is that there are two tables: F1 and F2.
>
> F1 has columns A, B, P,Q
> F2 has columns C, D, R, S
>
> They are related in that for each row in F1 there is a corresponding
> row in F2. joined on F1.A = F2.C and F1.B = F2.D
>
> Requirement is to update P & Q in F1 from R & S in corresponding row
> of F2 when either F1.P <> F2.R or F1.Q <> F2.S
>
> Deleting rows in F1 where this case is true is an easy EXISTS test.
>
> But how should an Update be done?
>
> I can do it in MS SQL server, using an UPDATE FROM construct, but this
> is rejected as syntactically invalid on the AS/400.
>
> So can anyone give me an SQL statement to do this on an AS/400 please?
>
> Thanks in advance :-)