Postgres: copy value of specific field from referenced table only for matching foreign keys-CodePudding

I currently have two tables in the same postgres database:

EntityA:

Column	type	nullable
id	uuid	false
value1	varchar	true
value2	varchar	true
entityB_id	foreign key	true

EntityB:

Column	type	nullable
id	uuid	false
value1	varchar	true
value2	varchar	true

I know need to copy the value1 and value2 from the EntityB table into the table of EntityA, but only for the lines where the EntityB id is referenced.

I tried the following without success:

insert into EntityA (value1, value2)
select EntityB.value1, EntityB.value2 
from EntityB
where EntityA.entityB_id = EntityB.id

I found other guides on how to copy data from one table to another, however not with a condition has to be met. I am currently at a loss on how to successfully copy that data. Any help is greatly appreciated!

CodePudding user response：

As far as I understand you want to update data in table A using data that is stored in B. e.g. you start with

A	id	value1	value2	entityB_id
	1	null	null	2

B	id	value1	value2
	2	'test'	'test2'

And expect the following result in A:

A	id	value1	value2	entityB_id
	1	'test'	'test2'	2

Instead of INSERT you need to use UPDATE as you want to modify existing rows:

UPDATE "EntityA" a
SET value1 = (
    SELECT b."value1"
    FROM "EntityB" b
    WHERE b.id = a."entityB_id"
),
value2 = (
    SELECT b."value2"
    FROM "EntityB" b
    WHERE b.id = a."entityB_id"
)
WHERE "entityB_id" IS NOT NULL