Question 115091 in PostgreSQL Question posted in
The official documentation can be found here.

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

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)
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search