I have to table named 'stuff' and 'given'. I wanted to add one more columnt to 'stuff' and after ALTER TABLE i used UPDATE. However this makes difference for only that moment. I mean, m new column will give the amount of stock after each given amount. 'stuff' has 'totalAmount' and given has 'amount'.
I used:
ALTER TABLE stuff ADD stock MEDIUMUNINT UNSIGNED;
UPDATE stuff SET stuff = totalAmount - (SELECT SUM(given.amount) FROM given WHERE id = given.productId);
This works for only that UPDATE. How can i make these two table so synchronized after each given amount, the stock will be affected either?
Thanks in advance :)