CODEWITHSUNDEEP
sqlpostgresqlselectinner-join
Toike
Toike

Reputation: 147

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

Upvotes: 7

Views: 12805

Answers (3)

user330315
user330315

Reputation:

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.

Upvotes: 16

Gordon Linoff
Gordon Linoff

Reputation: 1270081

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;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

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

Upvotes: 0

Related Questions