select "ScopeNO",count("ScopeNO") num from "Store" s inner join "InputLog" l on s.no = l."LogObjectNO" where l."InputTime" > date'2011-12-13' and l."LogObjectType"='Unit'group by "ScopeNO"上面的语句得出table1这个表table1
ScopeNO num
1 20
2 5
3 30
4 10
5 20table2
ScopeNO num
1 100
2 100
3 100
4 100
5 100现在是这样的 table1 和table2的 ScopeNO是有inner join 关系的现在要的结果是更新table2的num的数据 table2的num 减去 table1的num 如何实现?
ScopeNO num
1 20
2 5
3 30
4 10
5 20table2
ScopeNO num
1 100
2 100
3 100
4 100
5 100现在是这样的 table1 和table2的 ScopeNO是有inner join 关系的现在要的结果是更新table2的num的数据 table2的num 减去 table1的num 如何实现?
WHERE T2.ScopeNO = (SELECT ScopeNO FROM
(select "ScopeNO",count("ScopeNO") num
from "Store" s inner join "InputLog" l
on s.no = l."LogObjectNO"
where l."InputTime" > date'2011-12-13'
and l."LogObjectType"='Unit'
group by "ScopeNO") T1;
UPDATE TABLE2 SET NUM = NUM - (SELECT NUM FROM TABLE1 WHERE TABLE1.SCOPENO = TABLE2.SCOPENO );-- 方法二
UPDATE (SELECT T2.SCOPENO,T2.NUM,T2.NUM - T1.NUM NUMNEW FROM TABLE2 T2,TABLE1 T1 WHERE T2.SCOPENO = T1.SCOPENO)
SET T2.NUM = NUMNEW;