Subquery returned more than 1 value – STIntersects

I have three tables

• 2x tables of observational point data with a point geom column (for different time periods)
• A table of hexbins across the study area, with a polygon geom column

Both are in the same coordinate system.
The points tables are always intersecting the hexbin table. i.e. no points outside the hexbin layer.

Running the following query:

UPDATE OBS_MONDAY
SET GRID_ID = (
    SELECT GRID_ID
        FROM SYDHEX s with (index(FDO_Shape))
    WHERE (OBS_MONDAY.Shape.STIntersects(s.Shape) = 1))

This executes fine, and calculates the GRID ID field from the Hexbin layer into a column in the Point layer.

However, running this same query against the 2nd point table causes an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can someone help me pinpoint the problem here please.

Answer

You have to decide what to do. The “work-around” is to use SELECT TOP (1) or aggregation:

UPDATE OBS_MONDAY
    SET GRID_ID = (SELECT TOP (1) GRID_ID
                   FROM SYDHEX s with (index(FDO_Shape))
                   WHERE OBS_MONDAY.Shape.STIntersects(s.Shape) = 1
                  );

By the way, this has nothing to do with spatial data. You are trying to assign a single value to GRID_ID and your subquery is returning more than one value.