Using a Correlated Subquery to Update a Base Table

The syntax for updating using correlated subqueries can be a little confusing at times. The example shown below uses a table variable as its target.

The key is to include an aliased version of the table in the UPDATE’s FROM clause.

Once that is aliased, you can simply reference the alias in the subquery.

For example:
UPDATE
    x
SET
    PROJCOMMENTID = 
    (
        SELECT 
            MAX(pc.PROJCOMMENTID) 
        FROM 
            ProjectComments pc (NOLOCK) 
        WHERE 
            pc.PROJECTID = x.PROJECTID
    )
FROM
    @ProjComments x

Featured Photo by Maarten Deckers on Unsplash