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

Question

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 right values?

Answer 1

The target table of an update statement should never be repeated in the from clause

So I think you want this:

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

This assumes that counties.name and sites.county are both unique.