表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-03END = BEGIN + IN - OUT
BEGIN = 上一天END请问如何用一条SQL将表1更新到表2?
===============================
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-03END = BEGIN + IN - OUT
BEGIN = 上一天END请问如何用一条SQL将表1更新到表2?
解决方案 »
- <checkbox>赋值问题
- option的疑问
- 以下是纯JDBC连接数据库的代码,但连接不到数据库,抛出异常java.sql.SQLException: No suitable driver
- 执行getHibernateTemplate().find(sql.toString())抛java.lang.OutOfMemoryError
- jsp怎么做在线播放电影?
- 求助解答html或者struts标签都遇到的一个问题,有解决方法,但谁有合理的解释?
- 网站内容更新问题
- 关于spring jdbc与oracle里的sequence的一个问题(oracle新手)
- js错误
- 如何获取图片上传时的参数?
- 在线等,急,,中文编码。。。
- 问个在百度查没有弄明白的问题servlet,jsp,javaBean的联系
楼主【CHARGING】截止到2008-07-25 17:03:26的历史汇总数据(不包括此帖):
发帖的总数量:5 发帖的总分数:520 每贴平均分数:104
回帖的总数量:7 得分贴总数量:2 回帖的得分率:28%
结贴的总数量:5 结贴的总分数:520
无满意结贴数:0 无满意结贴分:0
未结的帖子数:0 未结的总分数:0
结贴的百分比:100.00% 结分的百分比:100.00%
无满意结贴率:0.00 % 无满意结分率:0.00 %
敬礼!
update test a set a.e_begin = ifnull(a.e_begin, (select b.e_end from test b where b.demand_date=date_add(a.demand_date, interval -1 day))),
a.e_end=ifnull(a.e_end, ((select c.e_end from test c where c.demand_date = date_add(a.demand_date, interval -1 day)) + a.e_in - a.e_out));Error Code : 1093
You can't specify target table 'a' for update in FROM clause
(0 ms taken)
(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)
mssql版本,用了cursor,破方法。-- 建表
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
[code=BatchFile]
b i o e d
----------- ----------- ----------- ----------- -----------------------
100 10 20 90 2008-07-01 00:00:00.000
90 20 30 80 2008-07-02 00:00:00.000
80 50 20 110 2008-07-03 00:00:00.000
110 50 20 140 2008-07-04 00:00:00.000(所影响的行数为 4 行)[/code]