"Updating" a SQL Inner Join Query

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
    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s