update feiyong set fy=fy-(select jf from jifei where kh=feiyong.kh) 这里没有考虑jifei的相应记录不存在,若为NULL应另加处理
UPDATE feiyong SET fy=fy-(SELECT NVL(jf) FROM jifei WHERE kh=feiyong.kh)
上面错了。不好意思。 UPDATE feiyong SET fy=fy-(SELECT NVL(jf,0) FROM jifei WHERE kh=feiyong.kh)
update feiyong a set fy=( select fee from ( select t1.kh kh,t1.fy-t2.jf fee from feiyong t1,jifei t2 where t1.kh=t2.kh )b where a.kh=b.kh ) where exists (select 1 from feiyong t1,jifei t2 where t1.kh=t2.kh )
我写的比较复杂,不过肯定对。如果表中数据有null值,注意使用decode()函数进行过滤。
update feiyong a set fy=fy-NVL(t2.jf,0)where exists (select 1 from feiyong t1,jifei t2 where t1.kh=t2.kh )
这里没有考虑jifei的相应记录不存在,若为NULL应另加处理
UPDATE feiyong SET fy=fy-(SELECT NVL(jf,0) FROM jifei WHERE kh=feiyong.kh)
from
( select t1.kh kh,t1.fy-t2.jf fee
from feiyong t1,jifei t2
where t1.kh=t2.kh
)b where a.kh=b.kh
)
where exists (select 1 from feiyong t1,jifei t2 where t1.kh=t2.kh )