update PFaids2005.dbo.work_study_tracking
set dept_fws=e.dept_fws,
supr_fws=e.supr_fws,
telephone_fws=e.telephone_fws,
email_fws=e.email_fws,
dept_sws=e.dept_sws,
supr_sws=e.supr_sws,
telephone_sws=e.telephone_sws,
email_sws=e.email_sws,
dept_iws=e.dept_iws,
supr_iws=e.supr_iws,
telephone_iws=e.telephone_iws,
email_iws=e.email_iws,
dept_ows=e.dept_ows,
supr_ows=e.supr_ows,
telephone_ows=e.telephone_ows,
email_ows=e.email_ows
from Manual.dbo.FWSP_Employment_Roll as e
where PFaids2005.dbo.work_study_tracking.stu_ay_token=e.stu_ay_token
If the record doesn't exist in the table being updated, I'd like to create a record with the values I'm selecting.
INSERT INTO yourtarget (col1, col2, col3, ... , coln)
SELECT yourvalues
FROM yoursource
WHERE NOT EXISTS (
SELECT 1 FROM yourtarget
WHERE col1 = yourval1 AND col2 = yourval2 AND ...
)
If you want to do this in one shot (i.e. do both the update and insert) and you're using SQL 2008 or higher, you may want to consider the MERGE statement.
The old Sybase UPDATE.. FROM.. does not work. I do not mean it is slow or proprietary; I mean it gives the wrong results. Google it. When you have a 1:m update, it grabs the last physical record in physical storage and uses it. It should give an error.
Please learn all the ways and rows and records are different. Look at the MERGE statement for this job.