Update fields with data from a second table

Question

I have a database in PostgreSQL and I'm having some problems updating some values, let me explain.

I have three tables.

Table A:

  id  |  itemID  |  Value

Table B:

  Name  |  Value

Table C:

   itemID  |  Name

I need to update the value field on table A with the value from table B where the itemId from the tables A and C are equal.

I don't really know how to explain, please ask me anything so I can explain it better

Answer 1

You need a join in the UPDATE statement:

update tablea 
set "Value" = b."Value"
from tableb b inner join tablec c
on c.name = b.name
where c."itemID" = tablea."itemID";

See the demo.

Answer 2

You can use Update statement with a Common Table Expression, and bring the results directly by adding returning clause at the end.

with t0 as
( 
 select c.itemID , b.value
   from tableC c
   left join tableB b on b.name = c.name
)
update tableA a
   set value = t0.value
  from t0
 where a.itemID = t0.itemID 
returning a.*;

Demo