SQL Server Performance

error msg return Ambiguous column name

Discussion in 'General DBA Questions' started by motioneye, Aug 24, 2005.

  1. motioneye New Member

    Hi,
    below is my sql statement that wil join and update the values, but it giving me error
    Ambiguous column name both table hold the same column name, so what should I do to
    get the sql statement running,
    can't change the column name coz it will effect many sp eror while running

    update i
    set
    i.packid=b.packid ,i.deckid =b.deckid
    from individual i
    inner join port b
    on i.msn=b.msn
    where i.empid=1120
    motioneye, Aug 24, 2005
    #2

  2. dineshasanka Moderator

    I ran follwing scripts

    create table individual(packid int,deckid int ,empid int,msn varchar(20))
    create table port(packid int,deckid int ,empid int,msn varchar(20))

    update i
    set
    i.packid=b.packid ,i.deckid =b.deckid
    from individual i
    inner join port b
    on i.msn=b.msn
    where i.empid=1120

    and insernt some data

    Then it gives me I ros updated message meaning that query is running perfectly

    :-(
    dineshasanka, Aug 24, 2005
    #2

  3. Adriaan New Member

    Try it without the i alias in the SET clause, like so:

    update i
    set packid = b.packid, deckid = b.deckid
    from individual i
    inner join port b
    on i.msn = b.msn
    where i.empid = 1120

    Adriaan, Aug 24, 2005
    #2

  4. FrankKalis Moderator

    Might be worth a try. But if I've seen this correctly, it should also work with the alias. What is the exact error message?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
    FrankKalis, Aug 24, 2005
    #2

  5. motioneye New Member

    i GIVING TRY as below statement

    update i
    set packid = b.packid, deckid = b.deckid
    from individual i
    inner join port b
    on i.msn = b.msn
    where i.empid = 1120

    running OK... but when it excuted as stored procedure
    an error msg pop us as below
    Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
    Ambiguous column name 'PackID'.
    Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
    Ambiguous column name 'DeckID'

    motioneye, Aug 24, 2005
    #2

  6. ghemant Moderator

    quote:Originally posted by motioneye

    i GIVING TRY as below statement

    update i
    set packid = b.packid, deckid = b.deckid
    from individual i
    inner join port b
    on i.msn = b.msn
    where i.empid = 1120

    running OK... but when it excuted as stored procedure
    an error msg pop us as below
    Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
    Ambiguous column name 'PackID'.
    Server: Msg 209, Level 16, State 1, Procedure Trial_insert_all, Line 288
    Ambiguous column name 'DeckID'



    it seems that you have post just a small portion of your sp ,because error message is for line no 288... may be it has more statements whick include the same columnname with same alias !
    can you specify more !?

    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
    ghemant, Aug 24, 2005
    #2

  7. Madhivanan Moderator

    Post the full query used in sp



    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Aug 24, 2005
    #2

Share This Page