Chris May

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:

UPDATE Job_Opportunities
SET AwardedId = 1
WHERE JobOpportunityId = (
    SELECT JO.JobOpportunityId 
    FROM Job_Opportunities JO
    INNER JOIN
    Job_results JobResults
    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.