The way I implemented my table, I registered all transactions to facilitate index and history. In index, I show only my positive stocks and they can be repeated if bought more than once (We also have to savet the price of each transaction). I sum the shares only to calculate and show the totals.

But, when I make sales I would have to zero each row of a specific stock until I exhaust the amount of sold shares. Any hints on how to make this UPDATE within SQL?

P.S. Before I sell, I check to see the amount of wanted shares to sell are less than the total available shares of that given stock.

1 Answer 1


It sounds like you are keeping multiple rows for multiple purchases of the same user and stock symbol combinations in the portfolio table. This requires searching all of those rows and subtracting from them for each sale.

Instead, why not keep one row for each userid/symbol combination and update the quantity as more shares are purchased or sold? This is easily implemented by first creating a primary or unique key based on the combination of those two columns. Then, when adding rows for purchases, use an ON DUPLICATE UPDATE clause.

Remember, the history table tracks the individual transactions, so they will be recorded there. The index table can keep track of the running totals.

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

  • If I understood your suggestion, that would mean keeping three tables: users, index and history. My implementation is nearly done, missing only this and the extra step. I'll think about it. Thanks.
    – B Furtado
    Commented Aug 29, 2017 at 13:33
  • what tables do you have now?
    – Cliff B
    Commented Aug 29, 2017 at 17:46
  • I was able to do it in a simpler way. I kept just one table for users and one table for portfolio in which I kept every transaction. Only, when at index.html I selected based on GROUPBY summing up the amount of shares. Just submitted! Everything working very fine. Nice exercise. Thanks
    – B Furtado
    Commented Aug 29, 2017 at 17:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .