Updating one table from another

• Scandalous

  SSC Enthusiast

  Points: 182

  More actions

  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)

• Neelakandan V-453229

  Ten Centuries

  Points: 1109

  More actions

  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.

• Scandalous

  SSC Enthusiast

  Points: 182

  More actions

  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!

• Christopher Stobbs

  SSC-Insane

  Points: 21098

  More actions

  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

• Neelakandan V-453229

  Ten Centuries

  Points: 1109

  More actions

  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.

• Scandalous

  SSC Enthusiast

  Points: 182

  More actions

  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!

• Scandalous

  SSC Enthusiast

  Points: 182

  More actions

  May 23, 2008 at 7:41 am

  #819668

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

• Neelakandan V-453229

  Ten Centuries

  Points: 1109

  More actions

  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.

• Scandalous

  SSC Enthusiast

  Points: 182

  More actions

  May 23, 2008 at 8:37 am

  #819721

  Thanks!