表A id name agenum 表B id age --有没有好的办法把新的age数量赋值给表A的agenum,也就是id是同一个值的B表的数量变化后更新到A表的agenum update A a set a.agenum=? where a.agenum!=(select count(*) from B b where a.id =b.id )
直接更新好了,即使相同也更新update A a set a.agenum=(select count(*) from B b where a.id =b.id )
update A a set a.agenum = (select age from B b where a.id = b.id) where exists( select 1 from A,B where A.id=B.id)
update A a set a.agenum=(select count(*) from B b where a.id =b.id ) ;
表b insert触发器 可以试试
其实。我还要更新name内容的,当agenum相等的时候就不更新了,不相等的时候就更新。
update A a set a.agenum=(select count(*) from B c where a.id =c.id ) where a.agenum!=(select count(*) from B b where a.id =b.id )
效率高的话,你要知道哪些做了变化, 可以先对B表进行统计,在获得A表达统计记录,对比,不同的再进行更新 --取出变化的,然后更新 SELECT id,CNT FROM A,(select ID,count(*) AS CNT from B GROUP BY ID) B WHERE A.ID=B.ID AND A.agenum<>B.CNT
where exists( select 1 from A,B where A.id=B.id)
where a.agenum!=(select count(*) from B b where a.id =b.id )
可以先对B表进行统计,在获得A表达统计记录,对比,不同的再进行更新
--取出变化的,然后更新
SELECT id,CNT
FROM
A,(select ID,count(*) AS CNT from B GROUP BY ID) B
WHERE A.ID=B.ID AND A.agenum<>B.CNT