求SQL更新语句
表1
===============================
BEGIN IN OUT END DATE
100 10 20 2008-07-01  
  20 30 2008-07-02
  50 20 2008-07-03

表2
===============================  
BEGIN IN OUT END DATE
100 10 20 90 2008-07-01  
90 20 30 80 2008-07-02
80 50 20 110 2008-07-03

END = BEGIN + IN - OUT
BEGIN = 上一天END

请问如何用一条SQL将表1更新到表2?

------解决方案--------------------
update table1 set BEGIN = (nvl(BEGIN,select END from table1 where DATE = trunc(DATE)-1)), END = (nvl(BEGIN,select END from table1 where DATE = trunc(DATE)-1)+IN-OUT);

不知道是否可以运行 手边没有oracle环境. 大概思路就是这个了，子查询应该可以优化.
------解决方案--------------------
本人在mysql5.0下调试过，可行。

insert into test2
(select (select max(begin) from test) +
IFNULL((select sum(t.IN) - sum(t.OUT)
from test t
where t.DATE < tt.DATE),
0) begin,
tt.in,
tt.out,
(select max(begin) from test) +
(select sum(t.IN) - sum(t.OUT) from test t where t.DATE <= tt.DATE) end,
tt.date
from test tt)

------解决方案--------------------
先select 后insert
------解决方案--------------------
考虑用procedure来实现
mssql版本，用了cursor，破方法。

SQL code

-- 建表
create table t1(
    b int,
    i int,
    o int,
    e int,
    d datetime default getdate()
)

-- 插入数据
insert into t1 
select 100,10,20,null,'2008-07-01' union all
select null,20,30,null,'2008-07-02' union all
select null,50,20,null,'2008-07-03' union all
select null,50,20,null,'2008-07-04'  

-- 存储过程
create procedure prDoSomething
as
declare @b int,
    @i int,
    @o int,
    @e int,
    @d datetime
declare cur cursor for
select b,i,o,d from t1
open cur
fetch next from cur into @b,@i,@o,@d
while(@@fetch_status = 0)
begin
    update t1 set e = @b + @i - @o where d = @d
    update t1 set b = @b + @i - @o where d = (select min(d) from t1 where d > @d)
    fetch next from cur into @b,@i,@o,@d
end
close cur
deallocate cur

-- 执行procedure
exec prDoSomething

-- 查询
select * from t1

-- 清理 
drop table t1
drop procedure prDoSomething