update with join – another version – postgreSQL

By | 5 November, 2010

This time its a second version of what i already posted, but might help some poor soul out there (and me, since i keep forgetting stuff).

UPDATE “Patrimonio”

SET x=st_x(“Morada”.geometry), y=st_y(“Morada”.geometry)

FROM “Morada”

WHERE “Patrimonio”.morada_id = “Morada”.”id”

This example is specifically for PostgreSQL, acting on a geometry point column, but can easily, or almost directly, ported to any other SQL in essence. What this does:
– Updates table “Patrimonio” with values taken from table “Morada”, where the id matches.
– Table “Patrimonio” has a column called “morada_id” that is used to fetch the corresponding geometry column on “Morada” table
More to read: