user7000826 user7000826 - 11 months ago 56
SQL Question

postgreSQL update from one Table to another based on a ID match

I have a database with

. Every site is located in one county. In the
table, the county is already mentioned but I want to replace it with the
, which in is the other table.

My update code is as follows:

UPDATE sites
SET cgid = c.gid
FROM (select c.gid as a from counties c
INNER JOIN sites s
ON = s.county) p;

The table
is updated, although every value in the
column is the same (the ID of the first county). What can I do to get the whole array?

Answer Source

You don't need a JOIN. Instead, you just need to connect the two tables in the WHERE clause:

UPDATE sites s
    SET cgid = c.gid 
    FROM counties c
    WHERE = s.county;