Search code examples
sqlpostgresqlforeign-keys

Insert Values from one Table into Another table where foreign key value = value from other table

I want to update all null values in the continent_name field in the Places table (pictured below), using the Countries table continent_name field values.

The country_id in the Places table is a foreign key to the Countries table (_Key). Ideally would only update the Places continent_name field with the Countries.continent_name values where there is a match btw Places.country_id = Countries._key

I tried something like this:

insert into Places (continent_name) select continent_name from Countries where Places.country_id=Countries._key;

The statement above doesn't like the where clause. (green fields are the foreign key to parent key relationship, yellow highlights show field values that would be updated)

enter image description here

Solution

  • if you want to update NULL value column you can do this as follows,

    UPDATE A SET A.continent_name=B.continent_name
FROM Places A INNER JOIN Countries B ON 
A.country_id =B._key 
WHERE A.continent_name IS NULL

    actually your schema is not proper , you need to use Places _key (primarykey) as a foreignkey inside Countries table instead of continent_name. like this Schema for dependent table

    anyways it depends on your requirements.