Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Unanswered: Updating one table column with data from other

    looks like a simple case but I'm confused:

    table_A column - date_A , primary key key_A
    table_B column - date_B, foreign key key_B

    I need to update every date_A with date_B.

    Update table_A
    set date_A =
    (select date_B
    from ?
    where ?
    )

    Thanks for your help.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2004
    Posts
    4
    From the link to the other thread I was still unable to find solution.

    I need to update each row of table_A with matching row from table_B.
    Updated column is date_A of table_A. It needs to take a value of date_B of table_B. Primary key is key_A and key_B.

    Thank you.

  4. #4
    Join Date
    May 2004
    Posts
    4
    Lenny,

    that case does not really makes sense:

    > need to update every date_A with date_B.

    Update table_A
    set date_A =
    (select date_B
    from table_B
    where table_A. date_A = table_B.date_B
    )

    means that column A is already the value of column B. So what do you want to do exactly? What is the correlation criteria for the update?

    cheers
    fynn

  5. #5
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Exclamation

    Fynn / Lenny,

    Code:

    Update table_A
    set date_A =
    (select date_B
    from table_B
    where table_A. date_A = table_B.date_B
    )

    would not work. I guess there is no dynamic mass update statement available.

    BUT Code:

    Update table_A
    set date_A =
    (select MAX(date_B)
    from table_B
    where table_A. date_A = table_B.date_B
    )

    would work because "select MAX(date_B) from table_B" returns one record, where as "select date_B from table_B" returns many records.

    I am still trying to figure out a way to do that. You might want to refer the DB2 cookbook. There are many examples available.

    Newbie

  6. #6
    Join Date
    Jun 2004
    Posts
    4
    Fynn,
    dsusendran is right on the money with my problem.
    I don't think that code
    Update table_A
    set date_A =
    (select MAX(date_B)
    from table_B
    where table_A. date_A = table_B.date_B
    )

    would serve my need.
    I am trying to update date from one table with date from the other table.
    This needs to be done thru correlated subquery which would do it for each row. However I am not sure how.

    Thank you all.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    create table table_A(keycol int,date_A date) ;

    create table table_B(keycol int,date_B date) ;

    select * from table_a
    1 2004-05-01
    2 2004-05-02
    10 2004-05-10

    select * from table_b

    2 2005-05-02
    1 2005-05-01
    3 2005-05-03

    update table_a a set date_a=(select date_b from table_b b where a.keycol=b.keycol)
    where exists (select 1 from table_b c where a.keycol=c.keycol)

    select * from table_A

    1 2005-05-01
    2 2005-05-02
    10 2004-05-10


    If you exclude the exisits clause (update table_a a set date_a=(select date_b from table_b b where a.keycol=b.keycol)
    )

    select * from table_a

    1 2005-05-01
    2 2005-05-02
    10 -



    Based on your requirement, you can choose one of the above statements ...

    Isn't this you want ?????

    If not, tell us what the resultset should be ...

    This query is the same as the one in the post I have referenced above

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    Update table_A
    set date_A = date_B


    This will update all the data in date_A with data in date_B.

    MN

  9. #9
    Join Date
    May 2004
    Posts
    4
    @all

    my post was to show that the query would not work, it wasn't a real suggestion.

    Famuda, your sample even does not make really sense because there is no corrleation (join) criteria. So Lenny, what excactly do you want to do?
    If the challenge is just to copy all values of column B to column A you are right, but is this really the requirement?

    Please submit more information about your aim.

    Is there a matching row in table B for every single row in table A? How do you match them? If you use the date as matching criteria then table A is already in the right state.


    cheers
    fynn
    Last edited by fynn00; 06-14-04 at 14:44. Reason: additional questions

  10. #10
    Join Date
    Jun 2004
    Posts
    4
    Fynn,
    I'm with you 100% about Famudba's post. I don't mean to be *** about it but it may confuse others who's trying to search before posting their problem.

    Date is not part of matching in my case.

    Here it is:

    UPDATE DBQ2.DB2DBA.ELC_PKG A
    SET DTE_ELC_END =
    (SELECT B.VEUC_END_DATE FROM DBQ2.DB2DBA.V3CRVEUC B,
    DBQ2.DB2DBA.ELC_PKG A
    WHERE A.CDE_WRLD_MFR = B.VEUC_W_MANUFACT_CD
    AND A.NUM_BAUM_1_3 = B.VEUC_FIN_BAUREIHE
    AND A.NUM_BAUM_4_6 = B.VEUC_FIN_DIGIT_4_6
    AND A.CDE_FIN_STR_PLANT = B.VEUC_FIN_DIGIT_7_8
    AND A.NUM_VEH_SERIAL = B.VEUC_FIN_SERIAL_NO
    AND B.VEUC_F_ACTIVE = '1'
    AND A.CDE_ELC_STAT = '006'
    AND SUBSTR(A.NAM_ELC_MBUSA,1,4) = SUBSTR(B.VEUC_CD,1,4)
    AND A.DTE_ELC_END = B.VEUC_END_DATE
    AND B.VEUC_END_DATE > CURRENT DATE)
    -- AND EXISTS
    WHERE EXISTS
    (SELECT 1 FROM DBQ2.DB2DBA.V3CRVEUC C
    WHERE A.CDE_WRLD_MFR = C.VEUC_W_MANUFACT_CD
    AND A.NUM_BAUM_1_3 = C.VEUC_FIN_BAUREIHE
    AND A.NUM_BAUM_4_6 = C.VEUC_FIN_DIGIT_4_6
    AND A.NUM_BAUM_4_6 = C.VEUC_FIN_DIGIT_4_6
    AND A.CDE_FIN_STR_PLANT = C.VEUC_FIN_DIGIT_7_8
    AND A.NUM_VEH_SERIAL = C.VEUC_FIN_SERIAL_NO
    AND C.VEUC_F_ACTIVE = '1'
    AND A.CDE_ELC_STAT = '006'
    AND SUBSTR(A.NAM_ELC_MBUSA,1,4) = SUBSTR(C.VEUC_CD,1,4)
    AND A.DTE_ELC_END = C.VEUC_END_DATE
    AND C.VEUC_END_DATE > CURRENT DATE
    );


    returns -811 (multiple rows)

    I thank you all for your help. This is graet thing forum like this one exists

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •