Refresh multiple columns from subquery?

Question

Avoiding duplicate subquery in the following T-SQL expression:

update r set column1=(select top 1 max(column1) from region r2 where (overlapping test conditions)), column2=(select top 1 max(column2) from region r2 where (overlapping test conditions)) from region r 

Basically, I have a table containing rectangle areas. For each overlapping region, I need to set some columns for the aggregation values ​​of this overlapping region.

thanks

EDIT: I add an overly simplified overlap condition here:

 (r.left >= r2.left and r.left < r2.right) or (r.right <= r2.right and r.right > r.left) 

Point: both r and r2 will be specified in the subquery. This seems like an ideal situation for a general table expression, but I cannot figure out how to use it for each record.

Answer 1

 UPDATE Region SET Region.Column1 = r.Column1 Region.Column2 = r.Column2 FROM Region INNER JOIN ( SELECT Column1, Column2 FROM Region WHERE (your condition here) ) r ON r.ID = Region.ID 

I just noticed MAX () agents. Sorry, did not see that for the first time. You may have to have two internal connections to make this work ...

take what i have and do it for column1

 INNER JOIN (SELECT MAX(Column1) FROM ... WHERE ... ) r ON r.ID = Region.ID 

Then the second INNER JOIN

 INNER JOIN (SELECT MAX(Column2) FROM ... WHERE ...)r2 ON r2.ID = Region.ID 

And your update will become

 UPDATE Region SET Region.Column1 = r1.Column1, Region.Column2 = r2.Column2 FROM Region INNER JOIN (SELECT MAX(Column1) as Column1 FROM ... WHERE ... ) r ON r.ID = Region.ID INNER JOIN (SELECT MAX(Column2) As Column2 FROM ... WHERE ... ) r2 ON r2.ID = Region.ID