请高手指点：怎么把2张表中相同金额的记录同步修改！对帐问题！（可能有多对多的情况！）（在线等待）

[sheyu8]

如何把a表和b表中money相等，并且a和b表state都等于0的记录，把他们的state都同步
改为1，不允许只改a表或者b表，必须全部改。
如果出现多对多的情况，按a,b表中都有的inputtime的时间最早的顺序一对一对的改掉。
请写出完整的过程。（可用游标及事务处理）

[pengdali]

begin tran
select [money] into #a from a in (select [money] from b where state=0) and state=0
update a set state=1 where [money] in (select [money] from #a)
update b set state=1 where [money] in (select [money] from #a)
commit tran

[friendliu]

用事务了
begin tran
select a.money into #a from a,b where a.money=b.money and a.state=0 and b.state=0

update a set state='1' where money in (select money from #a)
update b set state='1' where money in (select money from #a)

commit tran

[sheyu8]

这好象不行啊，如果a表有2个记录，而b表有4个记录
这样的话会把这总共6个记录都删掉，而对帐要求最多只能
删a表的2个记录，b表的2个记录！

[pengdali]

你举个例出来，你这样说连结构都不清楚，数据也没有描述。

[gmlxf]

--以下是我的测试

create table #a(money int,state int,inputtime datetime)
create table #b(money int,state int,inputtime datetime)
insert #a
select 100,0,'2003-1-1' union
select 100,0,'2003-1-2'

insert #b
select 100,0,'2003-2-1' union
select 100,0,'2003-2-2' union
select 100,0,'2003-2-3' union
select 100,0,'2003-2-4'


SET XACT_ABORT ON
declare @money int,@inputtime datetime,@inputtime2 datetime
declare myCursor CURSOR FOR
select inputtime from #a where state=0
open myCursor
fetch next from myCursor into @inputtime2
while @@FETCH_STATUS = 0
begin
begin tran
select top 1 @money=money,@inputtime=inputtime from #a where state=0 and inputtime=@inputtime2 order by inputtime
update #a set state=1 where money=@money and inputtime=@inputtime and state=0
if exists(select 1 from #b where money=@money and state=0)
begin
select top 1 @money=money,@inputtime=inputtime from #b where money=@money and state=0 order by inputtime
update #b set state=1 where money=@money and inputtime=@inputtime and state=0
commit tran
end
else
rollback
fetch next from myCursor into @inputtime2
end
close myCursor
deallocate myCursor
SET XACT_ABORT OFF

select * from #a
select * from #b

drop table #a
drop table #b

[gmlxf]

以上用到了事务处理，游标。不过条件是你的表中的inputtime的字段是唯一的。
如果不唯一，你取一个唯一的标志，修改一下程序就可以了。道理是按照上面的做。

[qdubit]

关注！

[sheyu8]

不行啊，如下所示：a表中有2条，和b中的4条记录满足条件，按要求这样只能删除a表和b表的前2条记录~！
a b
money state inputtime money state intputtime
100 0 2003-1-1 100 0 2003-2-1
100 0 2003-1-2 100 0 2003-2-2
100 0 2003-2-3
100 0 2002-2-4

[cheny1234]

关注一下！

[woxihuanbohe]

用触发器吧 a表变了 b表跟着便

[sheyu8]

gmlxf(烛光的回的程序我运行了下是正确的，但我作了以下的修改后
却出现了修改不统一的问题，即不是成对的修改数据，请问这是为什么？请指教
（假设a。b表的inputtime已经唯一的标识了一个记录）
create table #a(money int,state int,inputtime datetime)
create table #b(money int,state int,inputtime datetime)
insert #a
select 100,0,'2003-1-1' union
select 100,0,'2003-1-2'

insert #b
select 100,0,'2003-2-1'



SET XACT_ABORT ON
declare @money int,@inputtime datetime,@inputtime2 datetime
declare myCursor CURSOR FOR
select money,inputtime from #a where state=0 order by inputtime
open myCursor
fetch next from myCursor into @money,@inputtime
while @@FETCH_STATUS = 0
begin
select top 1 @inputtime2=inputtime
from #b where money=@money and state=0
order by inputtime asc
if (@inputtime2 is not null)
begin
begin tran
update #b set
state=1
where @inputtime2=inputtime

update #a
set state=1
where @inputtime=inputtime

commit tran
end
fetch next from myCursor into @money, @inputtime
end
close myCursor
deallocate myCursor
SET XACT_ABORT OFF

select * from #a
select * from #b

drop table #a
drop table #b