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