How to create a table in which the value of a field is based on the previous row in mysql

Question

Date | Column_A | Column_B Day 1 | 5 | 7 Day 2 | -3 | 7 + (-3) = 4 Day 3 | 8 | 4 + 8 = 12 Day 4 | -21 | 12 + (-21) -> 0 (see formula on row n) Day n-1 | ... | Column_B(n-1) Day n |Column_A(n) | IF(Column_B(n-1) + Column_A(n) >= 0, Column_B(n-1) + Column_A(n), 0) 

How to populate Column_B in mysql?

Answer 1

Can I add an Identity column to a table as an identifier? if so ... you can use below code:

  DECLARE @count INT; DECLARE @i INT; DECLARE @temp INT; SET @count =( select count(*) from myTBL) SET @i=2 WHILE (@i < =@count ) BEGIN SET @temp=(select Column_A from myTBL where ID=@i )+ (select Column_B from myTBL where ID=@i-1 ); IF (@temp>0) update myTBL set Column_B=@temp where ID=@i ELSE update myTBL set Column_B=0 where ID=@i SET @i = @i+1 END 

Answer 2

I create a Select that can bring this, I do not find a way to contribute it to the update.

 set @col_c:=7; select *,if(dt!=1, @col_c: =@col _c+if(col_a<0,col_a,col_a),0) as with_neg_values, if(@col_c<0,0,@col_c) as col_tot from t1 

SQLFIDDLE: http://www.sqlfiddle.com/#!2/105aa/3

Answer 3

Refresh request:

  update table1 firstInstance, tabe1 secondInstance set firstInstance.column_B = (IF(sIFNULL(secondInstance.column_B,0) + 12 >= 0, IFNULL(secondInstance.column_B,0) + 12, 0) +firstInstance.column_A) where DATEDIFF(secondInstance.date, firstInstance.date) = 1; 

SELECT query:

  SELECT firstInstance.DATE, firstInstance.COLUMN_A, (IF(IFNULL(secondInstance.column_B,0) + 12 >= 0, IFNULL(secondInstance.column_B,0)+ 12, 0) +firstInstance.column_A) AS COLUMN_B FROM table1 firstInstance LEFT JOIN tabe1 secondInstance ON DATEDIFF(secondInstance.date, firstInstance.date) = 1;