How to copy an element from another table if they share one common element in a column-CodePudding

I have a table containing two columns : Fruits & Color

Fruits	Color
Apple	Red
Orange	Orange
grape	NULL

On the other hand, i have another table, with only fruits, and an empty Color column.

Fruits	Color
banana	NULL
Orange	NULL
grape	NULL

My goal here is to parse the complete first table, and to add in the second one the color if the fruits are the same. To get something like this for my second table :

Fruits	Color
banana	NULL
Orange	Orange
grape	NULL

I've tried something like this, but it would only add into the second db all the rows from the first one that matches the same fruits

insert into "secondtable" (color)
select color
from "completetable" t1
where not exists (select 1 from "secondtable" t2 where t2.fruits = t1.fruits)

CodePudding user response：

You seem to want to update all rows of the second table with the values from the first table. As all fruits are null in the second table, you can simply

update secondtable
set color = (select color from firsttable where firsttable.fruit = secondtable.fruit);

That makes some unnecessary updates where null remains null, but is quick to write and understand.

CodePudding user response：

Specially on postgresql (and some other) also works and don't make unnecessarey updates:

UPDATE secondtable SET color = firsttable.color
FROM secondtable
  JOIN firsttable ON firsttable.fruit = secondtable.fruit
WHERE secondtable.color IS NULL AND firsttable.color IS NOT NULL;