I tried to update an attribute 'solved' of a polygon table (table name: polygon) by using an attribute 'solved' of another point table (table name: point).
What I want to do is that if ALL the points in a polygon has 'yes' in attribute 'solved', then the corresponding polygon will also have 'yes' in it's attribute 'solved', otherwise the polygon attribute 'solved' should have the value 'No'.
There are only 3 types of value for the attribute 'solved' in the point table: 'Yes', 'No' or Null.
So what I think how to build my SQL now, in other words, if a polygon contains any point with value 'No' its attribute solved, or has no value I the attribute, the polygon should return 'No' if its attribute solved. Otherwise the polygon will have the value 'Yes' in the attribute.
So I wrote something like that but it is not working:
UPDATE polygon IF ST_CONTAINS (point.geom, polygon.geom) AND (point.solved = 'No' OR point.solved is null) SET solved = 'No' ELSE SET solved = 'Yes' FROM point, polygon; END IF;
I got this error
ERROR: syntax error at or near "ST_CONTAINS"
Anyone has any clue what is wrong with that? Am I thinking correctly?
I know that this can be done easily in QGIS but I would like to figure it out since it will be part of my trigger when I update the point table.