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

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s