???Technique for using SQL to update one table from another??

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 :-)

Re: ???Technique for using SQL to update one table from another??

"CRPence" <crpence@vnet.ibm.com> wrote in message

news:4727af53$1@kcnews01...

<siip>[color=blue]

> 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) )[/color]

<snip>

Many thanks Chuck ........ sometimes SQL just isn't as "English like" as its

proponents make out.

One day IBM will go standard and provide the UPDATE x SET ....... FROM

............. WHERE...... construct which is much more "sensible"!