???Technique for using SQL to update one table from another?? - IBM AS400

This is a discussion on ???Technique for using SQL to update one table from another?? - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 2 of 2

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

  1. ???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 :-)



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


    "CRPence" wrote in message
    news:4727af53$1@kcnews01...

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



    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"!




+ Reply to Thread