Update data from a WITH clause on SQLite-CodePudding

I have a table **property **with records of postgis coordinates column geoarea(Points), and a bool column within_area(Bool) to determine whether its inside of that another postgis st_union(Polygon) from table urbanArea

select properties.id, ST_Within(properties.geoarea,st_transform("urbanArea"."st_union",2393)) from properties,"urbanArea"

which returns me all the records in properties by id, and bool value if its within the area or not. It takes about 10 sec to execute the query

I would like to now get the values from that select statement and insert it into the within_area column, I came up with this SQL query but it hangs forever and doesnt finish, any idea why?

UPDATE properties p
SET  within_area = (
with newarea as (select properties.id, ST_Within(properties.geoarea,st_transform("urbanArea"."st_union",2393)) as "isInside" from properties,"urbanArea")
select u."isInside" from newarea u  where u.id = p.id
)

I've as well tried doing it with CTE yet it still hangs forever.

with newarea as (select properties.id, ST_Within(properties.geoarea,st_transform("urbanArea"."st_union",2393)) from properties, "urbanArea")
UPDATE properties
SET 
    withinurban=newa.st_within
FROM properties prop
INNER JOIN
newarea newa
ON prop.id = newa.id

CodePudding user response：

Remove the extra join to properties in your 2nd query:

WITH newarea AS (
  SELECT p.id, 
         ST_Within(p.geoarea, ST_Transform(u."st_union", 2393)) st_within
  FROM properties p, "urbanArea" u
)
UPDATE properties p
SET withinurban = n.st_within
FROM newarea n
WHERE p.id = n.id;

But, your code seems equivalent to just:

UPDATE properties p
SET withinurban = ST_Within(p.geoarea, ST_Transform(u."st_union", 2393))
FROM "urbanArea" u;

or:

UPDATE properties p
SET withinurban = ST_Within(p.geoarea, ST_Transform((SELECT "st_union" FROM "urbanArea"), 2393));