user7000826 user7000826 - 1 month ago 8
SQL Question

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

I have a database with

sites
and
counties
. Every site is located in one county. In the
sites
table, the county is already mentioned but I want to replace it with the
ID
, 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 c.name = s.county) p;


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

Answer

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 c.name = s.county;
Comments