Spatialite - update query for bounding polygon using WITHIN

Question

I have 2 spatialite polygon layers which represent land parcels and municipalities. Each parcel is within or intersects 0, 1 or 2 municipalities. I am trying to update a field within the parcel layer with the name of the municipality when the centroid of parcel is within the municipality.

First I tried doing this within QGIS using the "Join attributes by location". It's very slow. I failed to make a proper test, and my attempt was for 1M parcels against 300 municipalities. It's about 2/3 done after 24 hrs and I'm afraid to stop it.

In the meantime I've been attempting it with a much smaller sample using SQL in the Spatialite-Gui application.

The following select query works:

Select qtrs.label, rm.rmname
from qtrs join rm on
within(centroid(qtrs.Geometry), rm.Geometry)

however I'm unable to convert it to a working update query. The following executes but puts all of the parcels into the same municipality

update qtrs
    set rmname=
(select rm.rmname
    from qtrs join rm where
    within(centroid(qtrs.Geometry), rm.Geometry));

Everything else I've tried fails with a syntax error

Answer 1

SQLite doesn't work like that, your inner select needs to return a single value for each row in the table to be updated. Try something like this:

    UPDATE qtrs  
      SET rmname=  
      (SELECT rm.rmname
        FROM qtrs q2 JOIN rm ON
        WITHIN(centroid(q2.Geometry), rm.Geometry)  
        WHERE qtrs.id=q2.id);

Thanks to @marcp for getting the syntax just right.

---

A note about spatial indexes from the OP. Spatialite, inexplicably, doesn't use them by default. For a query such as this they are crucial. I played around a bit and found a 30% improvement in speed by using the following. I was surprised, though, that there wasn't more of a gain.

update qtrs 
set rmname= (select rm.rmname from qtrs q2 
    join rm on within(centroid(q2.Geometry), rm.Geometry) 
    where  rm.rowid in (
        select ROWID from SpatialIndex
        where f_table_name='rm' and search_frame=qtrs.geometry)
    and qtrs.id = q2.id);