Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unhappy Unanswered: Query Help Please...

    I am trying to update some values in a table based on a translation table that I have built. I have tried two queries, one whose syntax will not work, and one with undesirable effects.

    1)
    UPDATE DEVDTA.LN_LEASERECORD
    SET DOCO = PRODDTA.TBL_LEASECOPY.NEWDOCO
    WHERE DEVDTA.LN_LEASERECORD.DOCO = PRODDTA.TBL_LEASECOPY.OLDDOCO;

    This is exactly what I would like the query to do...it is pretty self expanatory...however PRODDTA.TBL_LEASECOPY.NEWDOCO errors out as an undefined column.

    2)
    UPDATE DEVDTA.LN_LEASERECORD
    SET DOCO = (SELECT NEWDOCO FROM PRODDTA.TBL_LEASECOPY WHERE PRODDTA.TBL_LEASECOPY.OLDDOCO = DEVDTA.LN_LEASERECORD.DOCO);

    This runs however where the select by itself only selects matching records, in the update query, the 226 matching records are correctly updated will 8900+ records are changed to null.

    Can anyone tell me what I need to do differently?

    Thanks in advance.

    Ryan Hunt

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106
    Try adding
    WHERE DEVDTA.LN_LEASERECORD.DOCO IN (SELECT OLDDOCO FROM PRODDTA.TBL_LEASECOPY)

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

    Lightbulb


  4. #4
    Join Date
    Aug 2003
    Posts
    39
    That was simple...I feel rather silly...

    Thanks. RH

Posting Permissions

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