Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: update - table is ambiguous

    The following query returns the following error message:

    update itable
    set item_id=t1.item_id
    from itable t5, itable t1
    where t5.type_id=5
    and t1.type_id=1
    and t5.assoc_source_record_number=t1.source_record_num ber

    Server: Msg 8154, Level 16, State 1, Line 1
    The table 'itable' is ambiguous.

    Clearly, it doesn't know whether to update the type-1 records or the type-5 records. So how do I correct this? BOL doesn't give any examples where the join in the FROM clause is the same table twice.

    I want to set item_id in type 5 records to the item_id in the associated type 1 record.

    Any suggestions on how to correct the SQL appreciated. Thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    update t5

  3. #3
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78
    Thanks! :-)

Posting Permissions

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