Need help with Update query to populate new field

Hi folks:

I have added a new field to an established table, and am having trouble figuring out how to populate its values:

Two tables are involved: Jobs and Parts

There is a one-to-one relationship between each JobID and its PartID

Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim.

The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time:

UPDATE Jobs

SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question]))

WHERE (JobID = [the job in question])

My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time,

Matt

which is the parent table?

|||assuming that the jobs table has a foreign key that links to the parts table, the update query should look like this

UPDATE J
set j.JobPrice = p.PartPrice
from Jobs J
join Parts p on (p.PartID = j.PartID)|||

Thanks for your replies. I don't have any knowledge about foreign keys, etc. I attempted to set one up between the Jobs and Parts tables, but SQL Server Mgt Studio is still throwing errors when I attempt your query, carlop.

Thank you both anyway for throwing a pearl or two before this swine.

m