Results 1 to 4 of 4

Thread: query help

  1. #1
    Join Date
    Jul 2009
    Posts
    58

    Unanswered: query help

    Hello Guys,,

    This is my first post here...

    Actually i like to update a column from table A with a value from table B on some
    conditions. The query is as follows:

    update krms_Stg.SVHC_RAWTREESTRUCTUREDATAFORALL a
    set a.Wt_Fatherid =
    (select a1.value
    from krms_Stg.RAWTREESTRUCTUREDATAFORALL a1
    where a1.NODEID_FATHER=a.NODEID_SON and
    a1.TOP_LEVEL_NODE_ID=a.TOP_LEVEL_NODE_ID)
    where a.TOP_LEVEL_NODE_ID=a1.TOP_LEVEL_NODE_ID and
    a.NODEID_SON=a1.NODEID_SON and
    a.NODEID_FATHER=a1.NODEID_FATHER;

    But it is throwing an error saying the alias A1 is not recognized.

    Please help me out of this..

    Thanks for your help in advance,,

    Regards,
    Magesh.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    MERGE statement may help you.
    Like this:

    merge into krms_Stg.SVHC_RAWTREESTRUCTUREDATAFORALL a
    using krms_Stg.RAWTREESTRUCTUREDATAFORALL a1
    on a1.NODEID_FATHER = a.NODEID_SON
    and a1.TOP_LEVEL_NODE_ID = a.TOP_LEVEL_NODE_ID
    when matched then
    update set a.Wt_Fatherid = a1.value
    ;

  3. #3
    Join Date
    Jul 2009
    Posts
    58
    Thanks tonkuma... But still am having a problem..
    Am getting this error..
    SQL0788N The same row of target table "KRMS_STG.SVHC_RAWTREESTRUCTUREDATAFORALL" was identified more than once for an update, delete or insert operation of the MERGE statement. SQLSTATE=21506

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Doesn't
    Code:
    a1.NODEID_FATHER = a.NODEID_SON
    and a1.TOP_LEVEL_NODE_ID = a.TOP_LEVEL_NODE_ID
    identify a unique record ????

    IIRC, it has to - otherwise you get this message
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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