CODEWITHSUNDEEP
mysql
user7645195
user7645195

Reputation:

how to calculate a value of the last row with a new inserted value in mysql

All

how to calculate a value of the last row with a new inserted value in mysql

final_stock (2nd) row = final_stock(1st) row - out(2nd) row

i have a table

no   date         product_code   first_stock   in  out  final_stock
1    2018/01/18      A001          50          0   0      50 (last inserted)
2    2018/01/18      A001          0           0   35     15 <==== i want to achieve this

when I insert a new data (data no 2 ) and fill column "out" with 35, the "final_stock" column in 2nd row will have 15. how can I achieve that

this is the query

mysql_query("insert into flow_stock (date, product_code, first_stock, in, out, final_stock )
values('$date', '$code','','','$out',(select final_stock from flow_stock order by no desc limit 1) - ".$out.")");

need your help, the query doesn't calculate

Upvotes: 1

Views: 177

Answers (1)

Strawberry
Strawberry

Reputation: 33945

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(no INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,date DATE NOT NULL
,product_code CHAR(4) NOT NULL
,first_stock INT NOT NULL
,`in` INT NOT NULL
,`out` INT NOT NULL
,final_stock INT NOT NULL
);


INSERT INTO my_table VALUES (1,'2018-01-18','A001',50,0,0,50);

SELECT * FROM my_table;
+----+------------+--------------+-------------+----+-----+-------------+
| no | date       | product_code | first_stock | in | out | final_stock |
+----+------------+--------------+-------------+----+-----+-------------+
|  1 | 2018-01-18 | A001         |          50 |  0 |   0 |          50 |
+----+------------+--------------+-------------+----+-----+-------------+

INSERT INTO my_table (date,product_code,first_stock,`in`,`out`,final_stock) 
SELECT '2018-01-21'
     , 'A001'
     , 0
     , 0
     , 35
     , final_stock - 35 
  FROM my_table 
 WHERE product_code = 'A001' 
 ORDER 
    BY no DESC 
 LIMIT 1;

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM my_table;
+----+------------+--------------+-------------+----+-----+-------------+
| no | date       | product_code | first_stock | in | out | final_stock |
+----+------------+--------------+-------------+----+-----+-------------+
|  1 | 2018-01-18 | A001         |          50 |  0 |   0 |          50 |
|  2 | 2018-01-21 | A001         |           0 |  0 |  35 |          15 |
+----+------------+--------------+-------------+----+-----+-------------+

Upvotes: 1

Related Questions