CODEWITHSUNDEEP
sql-server-2005
Codism
Codism

Reputation: 6224

Update multiple columns from a subquery?

How can I avoid the duplicated subquery in the following T-SQL statement:

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 regions. For each overlapped region, I need to set some columns to the aggregation values of that overlapped region.

Thanks

EDIT: I am adding a over-simplified overlap condition here:

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

The point is: both r and r2 will be referenced in the subquery. It seems like this is a perfect situation for common table expression but I cannot figure out how to use it for each record.

Upvotes: 4

Views: 8107

Answers (1)

JonH
JonH

Reputation: 33153

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 the MAX() aggs. Sorry didnt see that the first time. You will have to probably have two inner joins to make that work...

so take what I have and do it for column1

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

Then a second INNER JOIN

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

And your update becomes

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

Upvotes: 1

Related Questions