Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Unanswered: Oracle Update Query

    Hello everyone,

    I'm trying to update a field (ASSET_HANSEN_CENTRELINE.STREET) with a street name that comes from the IMSV7.STREET table linked through the IMSV7.COMPSEG table by an STKEYfield.

    I've attached an ERD for further explaniation.
    Click image for larger version. 

Name:	ERD.jpg 
Views:	29 
Size:	12.3 KB 
ID:	10138
    here's my code so far

    UPDATE GISDBA.ASSET_HANSEN_CENTRELINE C SET C.STREET =
    (SELECT A.STNAME FROM IMSV7.STREET@PROD.WORLD A
    WHERE A.STKEY =
    (SELECT B.STKEY FROM IMSV7.COMPSEG@PROD.WORLD B
    WHERE (B.STKEY = A.STKEY) AND C.SEGID = B.MAPNO));

    what do you think? It runs forever so I'm not sure if its working.

    MB

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    UPDATE gisdba.asset_hansen_centreline c
    SET    c.street = (SELECT a.stname
                       FROM   imsv7.street@prod.world a
                       WHERE  a.stkey = (SELECT b.stkey
                                         FROM   imsv7.compseg@prod.world b
                                         WHERE  (b.stkey = a.stkey)
                                                AND c.segid = b.mapno));
    SQL appears OK but without DDL for tables & DML for test data looks can be deceiving.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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