Using STIntersects to update a table

I have two tables both with a geometry column:

Table t contains trees
Table ca contains polygonised conservation areas.

I'm trying to use SQL Server and STIntersects to work out when some of the trees fall within a conservation area.

After running my update query, trees that do fall within conservation areas still are being reported as outside. I think this is due to my conservation areas table containing multiple records.

A solution could be merging all the polygons in the Conservation Areas table, but I'm assuming there is better way of going about the issue.

UPDATE [TREES]
SET 
  IN_CONSERVATION_AREA = 
    CASE
      WHEN 
        t.[geom].STIntersects(ca.[geom]) = 1 THEN 'Y'
      WHEN
        t.[geom].STIntersects(ca.[geom]) = 0 THEN 'N'
     END

FROM [CONSERVATION_AREA] ca,[TREES] t

I'd expect the output to be 'Y' if a tree is in a conservation area and 'N' if it isn't.

Solution

You're joining all trees to all conservation areas. Presumably no tree is inside every conservation area so what the final result looks like depends on which update SQL Server chooses to apply¹ (since there's a separate Y or N update applicable for every combination).

What we could do instead is a LEFT JOIN using the intersects test:

UPDATE t
SET 
  IN_CONSERVATION_AREA = CASE
       WHEN ca.geom IS NULL
          THEN 'N'
       ELSE 'Y' END
FROM [TREES] t
  LEFT JOIN
    [CONSERVATION_AREA] ca
  ON t.[geom].STIntersects(ca.[geom]) = 1

If a tree is within multiple conservation areas, this will still attempt to update that tree multiple times but at least all of the updates are the same (Y).

Note also that in this new form, it's got the potential to benefit from Spatial Indexes, which you hopefully have.

As I said in the comments though, if you do have indexes, I'd suggest first testing whether performing this same logic when retrieving the data performs adequately. Prefer not to store data that can be computed - it just leaves open the possibility that it's out of date (when, say, someone adds a new conservation area)

¹Some database systems will complain with errors along the lines of "row subject to multiple update!". But not SQL Server. Instead it's documented to leave such rows updated in an arbitrary fashion.