update tshoupdjgl a set yusp = ( select sum(yuspj*shul) from tyuspsr b
where a.jilh=b.jilh and a.yusp<> sum(yuspj*shul) )
where caozlx='0'
and exists ( select 1 from tyuspsr c
where a.jilh=c.jilh and a.yusp<> sum(yuspj*shul) )
where a.jilh=b.jilh and a.yusp<> sum(yuspj*shul) )
where caozlx='0'
and exists ( select 1 from tyuspsr c
where a.jilh=c.jilh and a.yusp<> sum(yuspj*shul) )
SET YUSP = (SELECT SUM(YUSPJ * SHUL) AS JE
FROM TYUSPSR
WHERE JILH = TSHOUPDJGL.JILH
GROUP BY JILH)
WHERE CAOZLX = '0'
update tshoupdjgl set yusp=aaa.je
from(
select a.jilh,je from
(select jilh,yusp from tshoupdjgl where caozlx='0')a,
(select jilh,sum(yuspj*shul)as je from tyuspsr group by jilh )b
where a.jilh=b.jilh and yusp<>je
)aaa
where aaa.jilh=tshoupdjgl.jilh
set yusp = ( select sum(yuspj*shul) from tyuspsr b
where a.jilh=b.jilh and a.yusp<> sum(yuspj*shul) )
where caozlx='0'
and exists ( select 1 from tyuspsr c
where a.jilh=c.jilh and a.yusp<> sum(yuspj*shul) group by c.jijh )
set yusp = ( select sum(yuspj*shul) from tyuspsr b
where a.jilh=b.jilh)
where caozlx='0'
and exists ( select 1 from tyuspsr c
where a.jilh=c.jilh group by c.jijh having a.yusp<> sum(yuspj*shul) )
---------- ----------
2 3
1 4SQL> select * from t1; C1 C2 C3
---------- ---------- ----------
1 sdsadad 4
2 sdsadad 3
2 dsad 3SQL> update a2 a set val = ( select sum(c3) from t1 where c1=a)
2 where exists ( select 1 from t1 where c1 = a group by c1
3 having nvl(val,0) <> sum(c3));1 row updated.SQL> select * from a2; A VAL
---------- ----------
2 6
1 4SQL>
顺便问一下,在执行SQL语句的时候是不是这样进行词法分析的?
以
update tshoupdjgl a
set yusp = ( select sum(yuspj*shul) from tyuspsr b
where a.jilh=b.jilh)
where caozlx='0'
and exists ( select 1 from tyuspsr c
where a.jilh=c.jilh group by c.jilh having a.yusp<> sum(yuspj*shul) )
为例!
1.先根据where 条件一过滤,如这里是先找出符合
exists ( select 1 from tyuspsr c
where a.jilh=c.jilh group by c.jilh having a.yusp<> sum(yuspj*shul)条件的
tyuspsr表里的数据2.再在找出的这些数据里执行select sum(yuspj*shul) from tyuspsr b
where a.jilh=b.jilh 从而得到一个已经过滤了的结果,然后把此结果用set 句赋给yusp??如果不是,那它是如何执行的?谢谢对了我还想问一下为什么下列语句不行?
update tshoupdjgl set yusp=aaa.je
from(
select a.jilh,je from
(select jilh,yusp from tshoupdjgl where caozlx='0')a,
(select jilh,sum(yuspj*shul)as je from tyuspsr group by jilh )b
where a.jilh=b.jilh and yusp<>je
)aaa
where aaa.jilh=tshoupdjgl.jilh