MySQL存储过程_删除时更新另一张表计数?

无幻 2011-12-12 02:39:13
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

请问这样的用存储过程需要怎么样写?
...全文
358 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
ACMAIN_CHM 2011-12-13
  • 打赏
  • 举报
回复
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
//
无幻 2011-12-13
  • 打赏
  • 举报
回复
谢谢大家
WWWWA 2011-12-13
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 akof1314 的回复:]

引用 3 楼 wwwwb 的回复:

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来匹配才得到想要……
[/Quote]
update b inner join (select Value ,count(*) as gs from a where Date='2011-12-12' group by value) c ON A.VALUE=B.VALUE set b.Num=b.Num-c.gs;
delete from a where Date='2011-12-12'
无幻 2011-12-12
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 wwwwb 的回复:]

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'
[/Quote]
后面还需要ON来匹配才得到想要的呀
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 on b.Value=c.Value;
无幻 2011-12-12
  • 打赏
  • 举报
回复
[Quote=引用 2 楼 acmain_chm 的回复:]

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=a.Value;

delete f……
[/Quote]
a.Value; 不能引用吧,是where b.Value=t.Value;?
rucypli 2011-12-12
  • 打赏
  • 举报
回复
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'
wwwwb 2011-12-12
  • 打赏
  • 举报
回复
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'
ACMAIN_CHM 2011-12-12
  • 打赏
  • 举报
回复
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=a.Value;

delete from A where `Date`='2011-12-12' ;
end
//
wwwwb 2011-12-12
  • 打赏
  • 举报
回复
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 b where Date='2011-12-12'

56,940

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧