[postgis-users] Update field from expression

[Nicolas Ribot]

 Greetings PostGIS list,
>
> I am trying to populate a relate field (huc) in a point table
> (permit_sites) from the gid field in a multi-polygon table (huc8).
>
> This is what I came up with:
>
> UPDATE permit_sites SET huc = giddy FROM (SELECT h.gid FROM huc8 h,
> permit_sites p WHERE ST_Within(p.the_geom,h.the_geom)) AS giddy;
>
> Whence I get this response:
>
> ERROR:  column "huc" is of type integer but expression is of type record
> HINT:  You will need to rewrite or cast the expression.
>
> Seems I can't take the hint, I tried casting but it is apparently
> impossible to cast a record as an integer, at least for me.
>
> Any suggestions as to how I should proceed would be appreciated.
>

Hi, you have to name the field from the inner query called giddy:

UPDATE permit_sites SET huc = giddy.gid FROM (SELECT h.gid FROM huc8 h,
permit_sites p WHERE ST_Within(p.the_geom,h.the_geom)) AS giddy;

Nicolas