update from table to table

[dforkin]

How can I write a sql statement to update data in one table with data from another table.

For example:

I have a field named CALLER in a table called SURVEY.

I have a field named SURVEYEDBY in a table called CONTFILE.

These two tables are related as follows:

Survey.Contid=Contfile.Contactid

I would like to fill the value in Survey.Caller to the existing value in Contfile.SurveyedBy -- based on the above join.

I have the following query which does not seem to work. Error message tells me that it can't identify the table Contfile. What can I change to get it to work?

Update Survey
set survey.caller=contfile.surveyedby
where survey.contid=contfile.contactid;

Please Help.

 

[cmmrfrds]

This should do it. You can add a where if you need more qualification.

UPDATE Survey
set survey.caller = contfile.surveyed
FROM contfile INNER JOIN Survey
ON survey.contid = contfile.contactid