Problem with Inserting data in tables

[anser316]

Hi, I have a problem with inserting data into my 'total_stock' table. I want to calculate 'total_stock' by adding all 'stock'. I have been trying to insert data in 'stocks' table, within inserting data in 'total_stock' as i cant add into stock table first as i need the foreign which i havent created.

 

 

create table total_stock(
t_id                 smallint(2) unsigned not null,
total_stock    float not null,
constraint total_pk primary key (t_id));

create table stocks
s_id      smallint(2) unsigned not null,
stock    int not null,
t_id      smallint(2) unsigned not null,
constraint stocks_pk primary key (s_id)
constraint stocks_tid_fk foreign key (t_id)
REFERENCES total_stock(t_id));

[mwasif]

What you have tried so far?

[anser316]

insert into table_stock (t_id) values (1);

insert into stocks (s_id,stock,t_id) values (1,10,1);
insert into stocks (s_id,stock,t_id) values (2,20,1);

UPDATE table_stock SET total_stock=SUM(stock)
WHERE t_id=1

 

I dont know how to sum the stock and store it properly

 

 

[mwasif]

You need to use subquery to SUM.

UPDATE total_stock SET 
total_stock = (SELECT SUM(stock) FROM stocks WHERE stocks.t_id = total_stock.t_id)
WHERE t_id=1

[cooldude832]

why do u need to get a SUM if you can always retrieve it on demand???

[mwasif]

why do u need to get a SUM if you can always retrieve it on demand???

This approach is very usefull when you have million of rows and you need to fetch that data frequently.