CODEWITHSUNDEEP
sqloracle-databasesql-update
ivanz
ivanz

Reputation: 815

Oracle update statement using multiple columns

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

Upvotes: 3

Views: 997

Answers (2)

Clint Good
Clint Good

Reputation: 850

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

Upvotes: 2

Jasti
Jasti

Reputation: 947

Following work is based on your sql fiddle and mysql database.

Change all the decimal columns in your creation scripts from integers to decimal. Following are the updated creation scripts 

CREATE TABLE produkt
    (`PRODUKTID` int, `NAZOV` varchar(47), `VYROBNA_CENA` decimal(10,2));

CREATE TABLE zlozenie
    (`PRODUKTKID` int, `MATERIALID` int, `MNOZSTVO` decimal(10,5));

CREATE TABLE material
    (`MATERIALID` int, `CENA` decimal(10,2));

sqlfiddle with updated tables and queries : http://sqlfiddle.com/#!2/0242f/6

Query to update the produckt table

update produkt INNER JOIN
(select  ZLOZENIE.PRODUKTKID,sum(ZLOZENIE.MNOZSTVO*MATERIAL.CENA) as celkova
from MATERIAL, ZLOZENIE 
where   ZLOZENIE.MATERIALID=MATERIAL.MATERIALID
group by ZLOZENIE.PRODUKTKID) ZM
on produkt.PRODUKTKID = ZM.PRODUKTKID
set produkt.VYROBNA_CENA = ZM.celkova

Let me know if this is not what you are after

Upvotes: -1

Related Questions