I frequently get asked how you can update a join statement in SQL. Of course, the UPDATE syntax doesn’t allow for you do actually perform an update directly against a JOIN statement, but there are some things you can do to work around this problem.
Probably the best way is to do the update like the example below:
SET AwardedId = 1
WHERE JobOpportunityId = (
FROM Job_Opportunities JO
ON BO.JobOpportunityID = BR.Job_id
AND JR.is_us = 1
AND JR.rank = 1
AND JO.JobOpportunityId = Job_Opportunities.JobOpportunityId
This example is updating the Job_Opportunities table, but in the WHERE clause we can use a join to make sure that the only rows that are updated are those that meet the criteria of the join.