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