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?

What do you mean with "whole array"? is your `cgid` column an `array`? Or the `name` or `county` columns? — , Nov 02 '16 at 11:18

score 14 · Accepted Answer · edited Nov 02 '16 at 11:19

14

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.

edited Nov 02 '16 at 11:19

Tim Biegeleisen

502,043
27
286
360

answered Nov 02 '16 at 11:18

You were missing an alias ;-) – Tim Biegeleisen Nov 02 '16 at 11:19
That is indeed what I needed. Thanks! – Toik95 Nov 02 '16 at 11:33
I am not sure why alias in the `FROM` does not work in my case, I am using a Postgres 12. I have to use table name directly in the field reference to make it work. – Hantsy Feb 07 '23 at 04:53

score 0 · Answer 2 · answered Nov 02 '16 at 11:18

0

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

answered Nov 02 '16 at 11:18

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 3 · answered Nov 02 '16 at 11:19

0

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;

answered Nov 02 '16 at 11:19

Gordon Linoff

1,242,037
58
646
786

Really weird: You have the earliest timestamp but I never saw you post. – Tim Biegeleisen Nov 02 '16 at 11:34

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

3 Answers3