How can I add a TOP 1 and a PARTITION BY in a Sql UPDATE statement?

I have the following sql statement - it's trying to find all the zipcodes which our bike paths, intersect :-

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

Which is fine ... unless a bike path intersects two or more zipcodes (which happens a lot). So, I want to say, Get the zipcode which this bikepath MOSTLY intersects (I do have another list elsewhere for all bikpaths to zipcodes).

If this was a SELECT statemement, it would look something like this...

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.Boun开发者_运维百科dary.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

and then I can just add a TOP 1 WHERE RowNumber = 1 to make sure I get one row per BikePath .. which will have the zipcode which that bike path is mostly contained within/intersects.

I know that 2nd Sql statement looks hairy, with that Geo-Spatial stuff added .. but can someone help me with incorporating this into an UPDATE statement?

I don't use geo spatial queries, but couldn't you put your second query in common table expression and join it in on your row_number field =1?

;WITH CTE AS (
 SELECT a.BikePathId, c.ZipCodeId,
     Rnum =ROW_NUMBER() OVER (PARTITION BY a.BikePathId ORDER BY c.Boundary.STIntersection(d.Boundary))
 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)
UPDATE a
SET a.ZipCodeId = cte.ZipCodeId
FROM [dbo].[BikePaths] a
    INNER JOIN cte on cte.bikepathid =a.bikepathid and cte.rnum=1;