Postgresql – Add data from one table to another when data in two other specific fields are identical

postgresqlpostgresql-9.4

I'm working on populating a cadastral table with codes from a database that comprises historic information about farms. Since both tables contain thousands of rows I'm looking for ways to automate the process. The two tables don't always have a direct correlation but there are ways to update the cadastral table automatically.

The database is PostgreSQL 9.4

The relevant fields in table 1 are:

county_name 
farm_name 
new_field_for_fk

For the second:

county_name 
farm_name 
pk

Neither the county_name nor the farm_name are unique identifiers, but for 99% of the time the combination of the two is, and I want to update new_field_for_fk with the data in pk where county_name and farm_name are identical. Any thoughts?

Best Answer

You could try an UPDATE with a FROM clause, from your description of the problem.

Something like:

UPDATE table1 t1
SET new_field_for_fk = t2.pk 
FROM table2 t2 
WHERE t1.county_name = t2.county_name 
  AND t1.farm_name = t2.farm_name ;

Should update all rows based on the matching of the two keys across the tables.

Related Question