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

Question

I have two tables: "Customer" and "Order". The "Customer" table has an attribute called noOfPurchases, and the "Order" table has an attribute called quantity.

How do I UPDATE the Customer 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 the 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

ISBN	customerID	quantity
8670081874189	2901	30
333488387049	2901	20
6137027197872	3421	18
333488387049	3683	15

Customer

customerID	c_firstname	c_surname	noOfPurchases
2901	john	smith	null
3421	lisa	jones	null
3683	alan	jenkins	null

Answer 1

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 ;