SpatiaLite : update table with a spatial relationships

Question

In a SpatiaLite DB, I am trying to update the value of a set of entities of a layer (table1) selected by a spatial relationship (equals) with entities from another layer (table2)

I can perform a simple selection that take about 1 minute with this request :

SELECT table1.* FROM table1, table2
WHERE EQUALS(table1.geom, table2.geom);

I can perform the update but it takes about 1 hour with this request :

UPDATE table1
SET field1 = 1
WHERE table.id IN (
SELECT table.id FROM table1, table2
WHERE EQUALS(table1.geom, table2.geom));

When I try this request which I hope could take the same time as the first request :

UPDATE table1
SET field1 = 1
WHERE EQUALS(table1.geom, table2.geom);

I get this error :

SQL error: no such column: table2.geom

I wonder if I can do the update faster than the second request. If I do it with QGIS, first the selection, then the update it very fast but I need to do it with SpatiaLite.

Answer 1

The reason that your update query is taking so long is that you are running the first query (the SELECT table1.*...) again and again for each row of table1.

Why not just create a new table with your first query?

CREATE TABLE table3 as SELECT table1.* FROM table1, table2
WHERE EQUALS(table1.geom, table2.geom);
SELECT RecoverGeometryColumn(table3, ...);