Update Query - The table x is ambiguous

  • Hello,

    I am attempting to write a query to update the cost of an item, based on the cost of an item on the same invoice in one statement.

    However I receive the error:

    Server: Msg 8154, Level 16, State 1, Line 1

    The table 'dbo.INVOICE_ITEM' is ambiguous.

    UPDATE dbo.INVOICE_ITEM

    SET

    INVOICE_COST = CONSULT_MBS_COST.MBS_ITEM_FEE * 0.50,

    ITEM_STATUS_CODE = CONSULT.ITEM_STATUS_CODE

    FROM

    dbo.INVOICE INVOICE

    INNER JOIN

    dbo.INVOICE_ITEM TELEHEALTH ON

    INVOICE.INVOICE_ID = TELEHEALTH.INVOICE_ID

    INNER JOIN

    dbo.MBS_ITEM MBS ON

    TELEHEALTH.MBS_ITEM_NUMBER = MBS.MBS_ITEM_NUMBER AND

    MBS.ITEM_TYPE_CODE = 'M' AND

    MBS.TELEHEALTH_ITEM = 'Y'

    INNER JOIN

    dbo.INVOICE_ITEM CONSULT ON

    INVOICE.INVOICE_ID = CONSULT.INVOICE_ID

    INNER JOIN

    dbo.MBS_ITEM CONSULT_MBS ON

    CONSULT.MBS_ITEM_NUMBER = CONSULT_MBS.MBS_ITEM_NUMBER AND

    CONSULT_MBS.ITEM_TYPE_CODE = 'M' AND

    ISNULL(CONSULT_MBS.TELEHEALTH_ITEM,'N') = 'N'

    INNER JOIN

    dbo.MBS_ITEM_COST CONSULT_MBS_COST ON

    CONSULT_MBS.MBS_ITEM_NUMBER = CONSULT_MBS_COST.MBS_ITEM_NUMBER AND

    CONSULT.SERVICE_DATE BETWEEN CONSULT_MBS_COST.MBS_COST_START AND ISNULL(CONSULT_MBS_COST.MBS_COST_END, GETDATE() ) AND

    CONSULT_MBS_COST.COST_TYPE_ID = 10

    WHERE

    INVOICE.INVOICE_DATE IS NULL

    The table dbo.INVOICE_ITEM is referenced twice in the FROM clause.

    Is this at all possible?

    I was hopng to avoid writing something like:

    UPDATE dbo.INVOICE_ITEM

    SET

    INVOICE_COST = ( <select a new price here in a subquery> )

    FROM

    ETC ETC

  • Just a shot in the dark, but did you try this:

    UPDATE TELEHEALTH SET

    INVOICE_COST = CONSULT_MBS_COST.MBS_ITEM_FEE * 0.50,

    ITEM_STATUS_CODE = CONSULT.ITEM_STATUS_CODE

    FROM

    dbo.INVOICE INVOICE

    INNER JOIN dbo.INVOICE_ITEM TELEHEALTH

    ON INVOICE.INVOICE_ID = TELEHEALTH.INVOICE_ID

    INNER JOIN dbo.MBS_ITEM MBS

    ON TELEHEALTH.MBS_ITEM_NUMBER = MBS.MBS_ITEM_NUMBER AND

    MBS.ITEM_TYPE_CODE = 'M' AND

    MBS.TELEHEALTH_ITEM = 'Y'

    INNER JOIN dbo.INVOICE_ITEM CONSULT

    ON INVOICE.INVOICE_ID = CONSULT.INVOICE_ID

    INNER JOIN dbo.MBS_ITEM CONSULT_MBS

    ON CONSULT.MBS_ITEM_NUMBER = CONSULT_MBS.MBS_ITEM_NUMBER AND

    CONSULT_MBS.ITEM_TYPE_CODE = 'M' AND

    ISNULL(CONSULT_MBS.TELEHEALTH_ITEM,'N') = 'N'

    INNER JOIN dbo.MBS_ITEM_COST CONSULT_MBS_COST

    ON CONSULT_MBS.MBS_ITEM_NUMBER = CONSULT_MBS_COST.MBS_ITEM_NUMBER AND

    CONSULT.SERVICE_DATE BETWEEN CONSULT_MBS_COST.MBS_COST_START AND

    ISNULL(CONSULT_MBS_COST.MBS_COST_END, GETDATE() ) AND

    CONSULT_MBS_COST.COST_TYPE_ID = 10

    WHERE

    INVOICE.INVOICE_DATE IS NULL

  • wow, that worked.

    I had no idea you could use Alias's in the UPDATE clause.

    Thanks!

  • Lynn Pettis (11/9/2011)


    Just a shot in the dark, but did you try this:

    UPDATE TELEHEALTH SET

    INVOICE_COST = CONSULT_MBS_COST.MBS_ITEM_FEE * 0.50,

    ITEM_STATUS_CODE = CONSULT.ITEM_STATUS_CODE

    FROM

    dbo.INVOICE INVOICE

    INNER JOIN dbo.INVOICE_ITEM TELEHEALTH

    ON INVOICE.INVOICE_ID = TELEHEALTH.INVOICE_ID

    INNER JOIN dbo.MBS_ITEM MBS

    ON TELEHEALTH.MBS_ITEM_NUMBER = MBS.MBS_ITEM_NUMBER AND

    MBS.ITEM_TYPE_CODE = 'M' AND

    MBS.TELEHEALTH_ITEM = 'Y'

    INNER JOIN dbo.INVOICE_ITEM CONSULT

    ON INVOICE.INVOICE_ID = CONSULT.INVOICE_ID

    INNER JOIN dbo.MBS_ITEM CONSULT_MBS

    ON CONSULT.MBS_ITEM_NUMBER = CONSULT_MBS.MBS_ITEM_NUMBER AND

    CONSULT_MBS.ITEM_TYPE_CODE = 'M' AND

    ISNULL(CONSULT_MBS.TELEHEALTH_ITEM,'N') = 'N'

    INNER JOIN dbo.MBS_ITEM_COST CONSULT_MBS_COST

    ON CONSULT_MBS.MBS_ITEM_NUMBER = CONSULT_MBS_COST.MBS_ITEM_NUMBER AND

    CONSULT.SERVICE_DATE BETWEEN CONSULT_MBS_COST.MBS_COST_START AND

    ISNULL(CONSULT_MBS_COST.MBS_COST_END, GETDATE() ) AND

    CONSULT_MBS_COST.COST_TYPE_ID = 10

    WHERE

    INVOICE.INVOICE_DATE IS NULL

    The solution given by Lynn Pettis worked for me too. But i don't see anything wrong in OP Questions(I too had made the query in same manner). Would like to know from the expert over here why does the OP Query not worked.

  • Scott Thornton-407727 (11/9/2011)


    wow, that worked.

    I had no idea you could use Alias's in the UPDATE clause.

    Thanks!

    Hi experts,

    This solution is working for me as a butter on a hot pan. But I would like to know from you guys , when the alias works with an update why not the actual table name. I mean that if we use alias it works why not it works by specifying the table name.

  • Shadab Shah (4/7/2013)


    Scott Thornton-407727 (11/9/2011)


    wow, that worked.

    I had no idea you could use Alias's in the UPDATE clause.

    Thanks!

    Hi experts,

    This solution is working for me as a butter on a hot pan. But I would like to know from you guys , when the alias works with an update why not the actual table name. I mean that if we use alias it works why not it works by specifying the table name.

    Using the table name works as long as YOU DO NOT alias it in the FROM part of the query - if you do, you need to reference the table using only this alias name.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply