表tb:sDt,f1,f2,f3,f42011-9-1 12 23 543 543
2011-9-1 23 45 54 53
2011-9-1 42 53 98 532
2011-9-2 0 0 0 0
2011-9-2 0 0 0 0
2011-9-2 0 0 0 0
想用2011-9-1的所有数据来代替2011-9-2的所有对应字段的数据,应该如何写命令啊?
2011-9-1 23 45 54 53
2011-9-1 42 53 98 532
2011-9-2 0 0 0 0
2011-9-2 0 0 0 0
2011-9-2 0 0 0 0
想用2011-9-1的所有数据来代替2011-9-2的所有对应字段的数据,应该如何写命令啊?
delete from tb where sDt='2011-9-2'insert into tb(sDt,f1,f2,f3,f4)
select '2011-9-2',f1,f2,f3,f4 from tb
where sDt='2011-9-1'
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (sDt varchar(10),f1 int,f2 int,f3 int,f4 int)
insert into [tb]
select '2011-9-1',12,23,543,543 union all
select '2011-9-1',23,45,54,53 union all
select '2011-9-1',42,53,98,532 union all
select '2011-9-2',0,0,0,0 union all
select '2011-9-2',0,0,0,0 union all
select '2011-9-2',0,0,0,0--开始操作
delete from tb where sDt='2011-9-2'insert into tb(sDt,f1,f2,f3,f4)
select '2011-9-2',f1,f2,f3,f4 from tb
where sDt='2011-9-1'select * from [tb]--结束操作
drop table [tb]/*
sDt f1 f2 f3 f4
---------- ----------- ----------- ----------- -----------
2011-9-1 12 23 543 543
2011-9-1 23 45 54 53
2011-9-1 42 53 98 532
2011-9-2 12 23 543 543
2011-9-2 23 45 54 53
2011-9-2 42 53 98 532(6 行受影响)
insert into tb (xxx) select top(select count(1) from tb where date='2011-09-02') xxx from tb where date='2011-09-01';
detele from tb where date='2011-09-02' and f1=0 and f2=0 and ....-- 向表中插入09-01的数据 09-02的这么多条,再删除09-02以前的数据。