3

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

ivanz
  • 775
  • 2
  • 11
  • 31
  • you mean like this [http://stackoverflow.com/questions/14618703/update-query-using-subquery-in-sql-server](http://stackoverflow.com/questions/14618703/update-query-using-subquery-in-sql-server) – benji Dec 18 '14 at 22:41
  • 1
    @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 Dec 18 '14 at 22:44
  • @paqogomez but isn't it the same concept? `update table set column = subquery`? – benji Dec 18 '14 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 Dec 19 '14 at 07:04

2 Answers2

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
Clint Good
  • 820
  • 6
  • 14
-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

Jasti
  • 927
  • 5
  • 14