数据库更新语句
我insuredaccount表有四个字段
snjz snye bnye id_year
542.14 0 363.5 2006
2007
snjz 的2007年的值要是2006年bnye加snye
这个update怎么写
还有一个insuredaccountall表里
的一个字段en_fare要等于2007年的snjz的
值
不需要要同时更新
[解决办法]
UPDATE insuredaccount
snjz = (SELECT snjz + bnye FROM insuredaccount B WHERE id_year = '2006 ')
WHERE id_year = '2007 '
[解决办法]
--try
update insuredaccount set snjz=(select snye+bnye from insuredaccount where id_year=2006)
where id_year=2007
[解决办法]
--try
update insuredaccountall set en_fare=(select snjz from insuredaccount where id_year=2007)
where id_year=2007
[解决办法]
UPDATE insuredaccount
SET snjz=(SELECT snjz + bnye FROM insuredaccount WHERE id_year = '2006 ')
FROM insuredaccount
WHERE id_year = '2007 '
[解决办法]
关于第二个需求，在insuredaccount表上建立触发器
create trigger on insuredaccount
after update
as
if update(snjz)
update insuredaccountall
set en_fare=(select snjz from inserted where id_year= '2007 ')
go
[解决办法]
update insuredaccount a,insuredaccount b set a.snjz=b.bnye+b.snye where a.id_year=b.id_year+1
update insuredaccountall set en_fare=(select snjz from insuredaccount where id_year=2007)
[解决办法]
update insuredaccount set bnye = (select snye+bnye from insuredaccount where id_year=2006)
from insuredaccount,insuredaccountall where insuredaccountall.en_fare=insuredaccount.snjz
and insuredaccount.id_year=2007
[解决办法]
update insuredaccountall
set en_fare = (select snjz from insuredaccount where id_year= '2007 ')
第二个你给的条件少点什么比如这两个表之间应该有关联条件?
[解决办法]
update insuredaccountall a,insuredaccount b set a.en_fare=b.snjz where a.id_year=b.id_year
[解决办法]
楼上答案一致，楼主可以结贴了
