Post reply

Updating one table from another

  • May 23, 2008 at 5:43 am

    #191492

    Now i am having a problem i hope one of you can help me with. I am trying to update a table named OrderDetails having the field name of PDate. Update is supposed to come from a table named PartsPricing field name of PDate. this is what i came up with so far but i keep getting an error of:

    Msg 4104, Level 16, State 1, Line 1

    The multi-part identifier "dbo.PartsPricing.PartID" could not be bound.

    Any help would be appreciated. Here is th statement i am running as a query.

    UPDATE OrderDetails

    SET OrderDetails.PDate = (SELECT PartsPricing.PDate

    FROM PartsPricing

    WHERE OrderDetails.PartID = PartsPricing.PartID)

    WHERE EXISTS

    (SELECT OrderDetails.PartID

    FROM OrderDetails

    WHERE OrderDetails.PartID = PartsPricing.PartID)

  • May 23, 2008 at 7:14 am

    #819634

    Hi,

    You can try this as well:

    UPDATE A

    SET A.PDate = B.PDate

    FROM OrderDetails A INNER JOIN PartsPricing B on

    (A.PartID = B.PartID)

    Warm Regards,
    Neel aka Vijay.

  • May 23, 2008 at 7:20 am

    #819640

    Thank you for your Response!

    I followed your advice and i receive another error

    Msg 208, Level 16, State 1, Line 10

    Invalid object name 'OrderDetails'.

    Thanks for your help!

  • May 23, 2008 at 7:22 am

    #819644

    HI,

    Make sure you are connected to the correct database and that the table name is correct.

    That is basically saying that it can't find your OrderDetails table!

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • May 23, 2008 at 7:28 am

    #819651

    Make sure you give a fully qualified name for the table.. something like:

    [databasename].[dbo].OrderDetails

    Warm Regards,
    Neel aka Vijay.

  • May 23, 2008 at 7:29 am

    #819653

    Yeah i figuired it out i think the query is running right now. I had to put the servername.DBname.Schema.TableName in the statment.

    Thank you very much for your help and advice!

  • May 23, 2008 at 7:41 am

    #819668

    Is there away to get around typing the servername.DBname.Schema in a statement like that?

  • May 23, 2008 at 7:55 am

    #819684

    You can consider using a Synonym. For more info:

    http://msdn.microsoft.com/en-us/library/ms177544.aspx

    Warm Regards,
    Neel aka Vijay.

  • May 23, 2008 at 8:37 am

    #819721

    Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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