0

I have a table where some column value depend on previous column value.
Here is table sample data.

+----+--------+----------+--------------+--------------+
| ID | Demand | Arriaval | OpeningStock | ClosingStock |
+----+--------+----------+--------------+--------------+
|  1 |      0 |      200 |            0 |          200 |
|  2 |     50 |        0 |          200 |          150 |
|  3 |     50 |      100 |          150 |          200 |
|  4 |    100 |      300 |          200 |          400 |
+----+--------+----------+--------------+--------------+

You can see openingStock is previous closingStock. And closing stock is calculated as

Arrival + OpeningStock - Demand = ClosingStock

If I change any closingStock then I have to update all next rows.
For example if I change row id=1 closingStock then I have to update all next three rows. I have thousand of row everytime I need to update single record I have to update all next records.

Is there any kind of pointer in mysql we can point opening stock to previous closing stock.

Or is there anything which can update automatically all next records when I row cloasing stock is updated

3
  • Is there any kind of pointer in mysql we can point opening stock to previous closing stock. No. But there is LAG() function which can be used in the query (MySQL 8+ needed). is there anything which can update automatically all next records when I row cloasing stock is updated No.
    – Akina
    Jan 5 at 7:41
  • row which depend on previous row value - this is called as over-defined data, and this is not recommended. It is enough to store Demand and Arriaval values, and calculate OpeningStock and ClosingStock values in the query. Maybe, some base value may be stored - a value for some date for which it is guaranteed that data before this date will never change. And this base value will be used in the calculations.
    – Akina
    Jan 5 at 7:47
  • Please go to dbfiddle.uk and construct your table, some sample data and maybe show us exactly what you your question is? I'm not sure what you mean by have to update all next rows - are you saying that any change has to be propagated down every record below (after) it? I'm assuming that the ID field is a proxy for time? Jan 5 at 8:55

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.