34,838
社区成员




create procedure my_proc
as
begin
update a
set a.rice=a.rice + b.rice
from warehouse a
left join
(select whid,sum(rice) rice from millfarm group by whid) b
on a.id=b.whid
update millfarm set rice=0
end
exec my_proc
begin tran
update a set a.rice=isnull(b.rice,0) + a.rice
from 仓库 a
left join
(select whid,sum(rice) rice from 农田 group by whid) b
on a.id=b.whid
--如果只清rice字段
update 农田 set rice=0
if @@error!=0
rollback tran
else
commit tran
update a set a.rice=isnull(b.rice,0) + a.rice
from 仓库 a
left join
(select whid,sum(rice) rice from 农田 group by whid) b
on a.id=b.whid
--如果是删除所有农田记录
truncate table 农田
--如果只清rice字段
update 农田 set rice=0
update 仓库
set rice=rice+b.rice
from 仓库 a join (
select whid,sum(rice)as rice from 农田表 group by whid
)b
on a.id=b.whid
where a.id=23
update a set a.rice=isnull(b.rice,a.rice)
from 仓库 a
left join
(select whid,sum(rice) rice from 农田 group by whid) b
on a.id=b.whid
--如果是删除所有农田记录
truncate table 农田
--如果只清rice字段
update 农田 set rice=0