MySQL存储过程_删除时更新另一张表计数？
MySQL存储过程_删除时更新另一张表计数？
A表
ID Value Date
1 v1 2011-12-11
2 v2 2011-12-12
3 v1 2011-12-12
4 v1 2011-12-12
5 v1 2011-12-12
…
B表
Value Num
v1 10
v2 15
比如：删掉A表中Date为2011-12-12的记录，然后更新B表中对应Value的Num，删几条即Num减去多少
更新后的B表
Value Num
v1 7
v2 14
请问这样的用存储过程需要怎么样写？
delimiter //
create proceudre dd ()
begin
update B ,(select `Value` ,count(*) as cnt from A where `Date`=’2011-12-12′ group by `Value`) t
set b.Num=b.Num-t.cnt
where b.Value=t.Value;
delete from A where `Date`=’2011-12-12′ ;
end
//
探讨
引用:
update b inner join (select Value ,count(*) as gs from a where Date=’2011-12-12′ group by value) c set b.Num=b.Num-c.gs;
delete from a where Date=’2011-12-12′
后面还需要ON来匹配才得到想要……
