Oracle update statement using multiple columns

Question

I am trying to update price of my products, using another table which has product material and number of that material

my tables are produkt (PRODUKTID int, NAZOV varchar(47), VYROBNA_CENA int) TABLE material (MATERIALID int, CENA numeric); TABLE zlozenie (PRODUKTKID int, MATERIALID int, MNOZSTVO int);

what i am trying to do is to put sum(ZLOZENIE.MNOZSTVO*MATERIAL.CENA) to column VYROBNA_CENA

i have two selects that are returning same column, but i am not sure how to use update to transfer values from one to another

first one - calculated price of products

select   PRODUKT.NAZOV as NAZOV, sum(ZLOZENIE.MNOZSTVO*MATERIAL.CENA) as celkova
from MATERIAL, PRODUKT, ZLOZENIE 
where   ZLOZENIE.MATERIALID=MATERIAL.MATERIALID
 and     PRODUKT.PRODUKTID=ZLOZENIE.PRODUKTKID
group by PRODUKT.NAZOV
order by PRODUKT.NAZOV

second one is table produkt with empty price(cena), and i would like to put results from sum to column cena

select  PRODUKT.NAZOV, PRODUKT.vyrobna_cena
from PRODUKT 
order by PRODUKT.NAZOV

sql fiddle with tables and queries http://sqlfiddle.com/#!2/e183f/2

thanks

Answer 1

MERGE INTO produkt p
  USING 
(
  SELECT ZLOZENIE.PRODUKTKID, SUM(ZLOZENIE.MNOZSTVO*MATERIAL.CENA) AS celkova
    FROM MATERIAL, ZLOZENIE 
    WHERE   ZLOZENIE.MATERIALID=MATERIAL.MATERIALID
    GROUP BY ZLOZENIE.PRODUKTKID
) s
ON (p.PRODUKTID = s.PRODUKTKID)
WHEN MATCHED THEN 
UPDATE SET VYROBNA_CENA = s.celkova;

Although I would always use join syntax

MERGE INTO produkt p
  USING 
(
  SELECT ZLOZENIE.PRODUKTKID, SUM(ZLOZENIE.MNOZSTVO*MATERIAL.CENA) as celkova
    FROM MATERIAL
      JOIN ZLOZENIE ON ZLOZENIE.MATERIALID=MATERIAL.MATERIALID
    GROUP BY ZLOZENIE.PRODUKTKID
) s
ON (p.PRODUKTID = s.PRODUKTKID)
WHEN MATCHED THEN 
UPDATE SET VYROBNA_CENA = s.celkova