Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    19

    Unanswered: Update table using join

    DB2 LUW 9.7.3

    I need some SQL help. I was sent a "master list" today of all the technician zones for each of our clients, the zones are assigned to the technician based on the client's zip code.

    I loaded the master list to the table TMP_FS_ZONES with the columns POSTAL_CODE and FS_ZONE to use as a look up table for my update.

    I need to update the FS_ZONE column of the table RS_OUTLET with the FS_ZONE value found in the TMP_FS_ZONES table all while matching up to the POSTAL_CODE that is found in a separate table RS_ADDRESS

    Any help would be appreciated, I may have started this entire process wrong by creating the TMP_FS_ZONES table which may be causing the confusion on my part...

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MERGE statement may be an answer.
    Join TMP_FS_ZONES and RS_ADDRESS in USING clause.

  3. #3
    Join Date
    Nov 2011
    Posts
    19
    I tried an update...

    UPDATE RS_OUTLET
    SET FS_ZONE =
    (SELECT FS_ZONE FROM TMP_FS_ZONES
    WHERE RS_OUTLET.OUTLET_OID = RS_ADDRESS.OWNER_OBJECT_OID AND TMP_FS_ZONES.POSTAL_CODE = RS_ADDRESS.POSTAL_CODE)


    Which returned the SQL0206N error:

    SQL0206N "RS_ADDRESS.OWNER_OBJECT_ID" is not valid in the context where it is used.

  4. #4
    Join Date
    Nov 2011
    Posts
    19
    I didn't end up doing it with the all in one statement approach I wanted to but I managed to get it updated. Thank-you for your help!

    First I merged TMP_FS_ZONES with RS_ADDRESS, then updated RS_OUTLET from RS_ADDRESS... Nothing fancy, but it worked.
    Last edited by Major_Data-Miner; 12-07-11 at 15:35.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MERGE statement may be an answer.
    Join TMP_FS_ZONES and RS_ADDRESS in USING clause.
    I already answered.
    Please use MERGE statement.
    It is not neccesary to use multiple steps/statements.
    One MERGE statement will be enough for your requirements.

    See
    page 24-27 in SQL on Fire! Part 2
    http://sirdug.org/downloads/SQLonFire_2_SirDUG.pdf
    or
    Information Center
    MERGE - IBM DB2 9.7 for Linux, UNIX, and Windows
    Last edited by tonkuma; 12-07-11 at 21:06.

  6. #6
    Join Date
    Nov 2011
    Posts
    19
    I would have loved to do it all with a single merge statement. Unfortunately, there was some unforseen data staging requirements I ran into once I looked more closely at the RS_ADDRESS table. Thank you for your help once again in helping me fight through a nearly career fatal case of brain fart.

Posting Permissions

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