update 仓库 set rice=rice+b.rice from 仓库 a join 农田表 b on a.id=b.id
update b set rice=a.rice from 农田表 a,仓库表 b where a.关联字段=b.关联字段
对应关系? 一对一?一对多?多对多?单值更新?avg更新???
农田表,里面有个字段:rice 仓库表,里面也有个字段:rice 怎么把农田里的rice,搬到仓库的rice中去呢???怎么更新2个表呢? 这不是更新两个表,应该对应主键或其他更新相关字段.update 仓库表 set rice = b.rice from 仓库表 a, 农田表 b where a.主键 = b.主键.
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
现在我有3块田,每块有200的rice,都指定whid=23的仓库. 仓库id=23 运行sql后, 仓库的rice只增加了200.应该加600的??? ---------------------------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,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 t set t.rice = t1.rice from a t , b t1 where t.id = t1 .id
仓库表:warehouse id ,rice农田表:millfarm id,rice,whid 能给个完整点的sql吗?
感谢fcuandy ,fa_ge ,happyflystone .....fcuandy的方法可以用了,呵呵. 还有我写到数据库的定时作业中,是不是要用事务包起来?update a set a.rice=a.rice + b.ricefrom warehouse aleft join (select whid,sum(rice) rice from millfarm group by whid) bon a.id=b.whidupdate millfarm set rice=0事务包起来怎么写呢?
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
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 endexec my_proc
他不是保护数据,是将一组SQL语句.简单的说就是一句不能搞定数据的多个SQL语句组合在一起的东西.
不好意思 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 endexec my_proc
不能做个事件触发,让它搬过去吗?当条件满足时就执行update 农田表 set rice=0 where whid in (select id from 仓库)
set rice=rice+b.rice
from 仓库 a join 农田表 b
on a.id=b.id
set
rice=a.rice
from
农田表 a,仓库表 b
where
a.关联字段=b.关联字段
一对一?一对多?多对多?单值更新?avg更新???
仓库表,里面也有个字段:rice
怎么把农田里的rice,搬到仓库的rice中去呢???怎么更新2个表呢? 这不是更新两个表,应该对应主键或其他更新相关字段.update 仓库表
set rice = b.rice
from 仓库表 a, 农田表 b
where a.主键 = b.主键.
仓库也有很多个.
农田表中有个字段(whid)放仓库的id.这样好象农田就能找到放rice的仓库.但是sql我写不好.
要是说不清楚,就给几条数据,说你要什么结果就可以了.
update 仓库
set rice=rice+b.rice
from 仓库 a join 农田表 b
on a.id=b.whid
set
rice=a.rice
from
(select 关联字段,sum(rice) as rice from 农田表 group by 关联字段) a,仓库表 b
where
a.关联字段=b.关联字段
--搬完了还要把农田的rice清空...谢谢
update 农田表
set rice=0
where whid in (select id from 仓库)
set rice=0
??????????????
仓库表 StorageTab update StorageTab
set rice=b.rice
from FarmTab a,StorageTab b
where a.whid=b.id
create trriger 名 on 仓库
after update
as
begin
update 农田表
set rice=0
where whid in (select id from inserted)
end
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
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,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
set t.rice = t1.rice
from a t , b t1
where t.id = t1 .id
id ,rice农田表:millfarm
id,rice,whid
能给个完整点的sql吗?
还有我写到数据库的定时作业中,是不是要用事务包起来?update a set a.rice=a.rice + b.ricefrom warehouse aleft join
(select whid,sum(rice) rice from millfarm group by whid) bon a.id=b.whidupdate millfarm set rice=0事务包起来怎么写呢?
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
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
endexec my_proc
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
endexec my_proc