mysql根据一列更新另一列_从同一个表中的另一列和行更新mysql中的列

I'm working with accounting pack in java and i wanna generate reports just using queries without java codes.

I wrote a stored procedure to generate a result table using many data tables.

it has 5 columns. name, date, credit, debit and balance.

I wrote a query to fill data from other tables. But i couldn't calculate balance from credit and debit.

Balance = last balance + debit - credit;

columns should be "credit", "debit" and "balance".

eg :-

CREDIT, DEBIT, BALANCE

100 ,0 , 0

50 ,0 , 0

0 ,200 , 0

120 ,0 , 0

100 ,0 , 0

0 ,100 , 0

0 ,100 , 0

After process table row values should be

CREDIT, DEBIT, BALANCE

100 ,0 , -100

50 ,0 , -150

0 ,200 , 50

120 ,0 , -70

100 ,0 , -170

0 ,100 , -70

0 ,100 , 30

Thank you!

解决方案I have tried this based on the example in my comments...

update people set balance = (select newbalance from (select (balance + debit) - credit as newbalance from people));

I have assumed a people's table. I have used the example in my comments and it updates the table with 350; Please play around and see what you come up with.

Hope it helps.

I Found a solution. I could write a stored procedure for it!

And also i added another primary key field named "id".

CREATE PROCEDURE ManageCreditorBalance()

BEGIN

declare a_id int;

declare a_val double default 0;

declare a_balance double default 0;

declare done int default 0;

declare cur1 cursor for select id, credit*-1 as val from creditors where debit = 0

union all

select id, debit as val from creditors where credit = 0;

declare continue handler for not found set done=1;

set a_balance = 0;

open cur1;

igmLoop: loop

fetch cur1 into a_id, a_val;

if done = 1 then leave igmLoop;

end if;

set a_balance = (a_balance + a_val);

update creditors set balance = a_balance where id = a_id;

end loop igmLoop;

close cur1;

END

And I found another solution from codeproject friends help.

This is it.

select a.addedDateTime, a.credit, a.debit, a.balance from creditors a limit 1

union all

select a.addedDateTime, a.credit, a.debit, b.balance + a.debit - a.credit as balance

from creditors a inner join creditors b

on a.id - 1 = b.id