title: How can I add a TOP 1 and a PARTITION BY in a Sql UPDATE statement?
body: <p>I have the following sql statement - it's trying to find all the zipcodes which our bike paths, intersect :-</p>
  
  <pre><code>UPDATE a
  SET a.ZipCodeId = d.ZipCodeId
  FROM [dbo].[BikePaths] a
      INNER JOIN [dbo].[BikePathBoundaries] b ON b.ZipCodeId = c.ZipCodeId
      INNER JOIN [dbo].[ZipCodeBoundaries] c ON b.Boundary.STIntersects(c.Boundary) = 1
  </code></pre>
  
  <p>Which is fine ... unless a bike path intersects two or more zipcodes (which happens a lot). So, I want to say, <code>Get the zipcode which this bikepath MOSTLY intersects</code> (I do have another list elsewhere for all bikpaths to zipcodes).</p>
  
  <p>If this was a <code>SELECT</code> statemement, it would look something like this...</p>
  
  <pre><code>SELECT a.BikePathId, a.BikePathName, c.ZipCodeId, d.ZipCode,
      c.Boundary.STIntersection(d.Boundary).STArea() AS Area,
      ROW_NUMBER() OVER (PARTITION BY a.BikePathId ORDER BY c.Boundary.STIntersection(d.Boundary).STArea() DESC) AS RowNumber
  FROM [dbo].[BikePaths] a
      INNER JOIN [dbo].[BikePathBoundaries] b on a.BikePathId = b.BikePathId
      INNER JOIN [dbo].[ZipCodeBoundaries] c on b.Boundary.STIntersects(c.Boundary) = 1
      INNER JOIN [dbo].[ZipCodes] d on c.ZipCodeId = d.ZipCodeId
  </code></pre>
  
  <p>and then I can just add a <strike><code>TOP 1</code></strike> <code>WHERE RowNumber = 1</code> to make sure I get one row per BikePath .. which will have the zipcode which that bike path is mostly contained within/intersects.</p>
  
  <p>I know that 2nd Sql statement looks hairy, with that Geo-Spatial stuff added .. but can someone help me with incorporating this into an <code>UPDATE</code> statement?</p>