Simple Update Query with Joins 1

[JohnnyLong]

I have never been able to get my head around Update queries with joins. Can anyone help? I need to update the text field CVText on table rc_CVData with tblApplicants2.CV_TEXT. I have to join these two tables through table rc_Candidates using the Key Applicants_APP_ID.

UPDATE rc_CVData

SET rc_CVData.CVText = tblApplicants2.CV_TEXT

FROM rc_Candidates
RIGHT JOIN rc_CVData.CandidateID = rc_Candidates.CandidateID
RIGHT JOIN rc_Candidates.Applicants_APP_ID = tblApplicants2.Applicants_APP_ID

I think I have to use aliases!

Any help much appreciated.

 

[vongrunt]

The question is: why right joins?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JohnnyLong]

Sorry, I did the query in access and copied the generated procedure. They don't have to be right joins.

 

[amrita418]

Try this:

UPDATE 		a
SET 		a.CVText = c.CV_TEXT
FROM 		rc_CVData a, 
		rc_Candidates b,  
		tblApplicants2 c
WHERE  		a.CandidateID = b.CandidateID
		and  b.Applicants_APP_ID = c.Applicants_APP_ID
                -- and a.CandidateID = 'blah blah'

PS: Always add a where clause to test one value to make sure update works well before running it on all.

Regards,
AA

 

[JohnnyLong]

Thanks alot AA,

Have a star!!

John