How do I get a data from one table to another but with calculations?-CodePudding

So I have CUSTOMER table and ORRDER table. CUSTOMERS table has an attribute called noOfPurchases. And ORRDER table has an attribute called quantity.

How do I UPDATE CUSTOMERS table in the way that, every customer will have a sum of their all orders quantity, for example one time there is a order of 5 items and another record with same customerID with 12 items. So it means noOfPurchases in CUSTOMERS table should be 17. And all customers data should be updated it is not that I should put as a input customerID one by one. So how can I do that?

ORDER table

CUSTOMER

CodePudding user response：

This is a straightforward multi table UPDATE as described in the manual page https://dev.mysql.com/doc/refman/8.0/en/update.html

update customer 
join (select customerid, sum(quantity) qty from `orrder` group by customerid) csum
on  customer.customerid = csum.customerid
set nofpurchases = csum.qty;

You can either run this manually or periodically using https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

or if you wish to update on an insert to orrder use a trigger https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html

delimiter $$
create trigger t after insert on `orrder`
for each row
begin
 update customer
  set nofpurchases = nofpurchases   new.quantity
  where customer.customerid = new.customerid;
end $$
delimiter ;