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

you mean like this http://stackoverflow.com/questions/14618703/update-query-using-subquery-in-sql-server — benji
– benji, Commented Dec 18, 2014 at 22:41
@benji please note that this article is oracle and your link is sql server. There may be a more specific way to handle this based on platform. Additionally your link does not show how to handle aggregation. — crthompson
– crthompson, Commented Dec 18, 2014 at 22:44
@paqogomez but isn't it the same concept? update table set column = subquery? — benji
– benji, Commented Dec 18, 2014 at 22:46
@benji - same concept but Oracle doesn't support INNER JOIN syntax in UpDATEs (although it would be highly neat if it did) — APC
– APC, Commented Dec 19, 2014 at 7:04

Clint Good · Accepted Answer · 2014-12-19 06:19:55Z

2

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

answered Dec 19, 2014 at 6:19

Clint Good

8606 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jasti · Accepted Answer · 2014-12-18 23:19:40Z

-1

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

answered Dec 18, 2014 at 23:19

Jasti

9475 silver badges14 bronze badges

3 Comments

ivanz Over a year ago

thanks, i am getting ORA-00971: missing SET keyword error. i will try to find out where is problem..

APC Over a year ago

MySQL syntax is different from Oracle syntax. So this Solution won't work, alas.

Jasti Over a year ago

Sevdah, Are you not using MySQL?

Collectives™ on Stack Overflow

Oracle update statement using multiple columns

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related