mysql 根据查询条件进行update

-- 新增comment_num 评论数字段
alter table tb_tips add   `comment_num` int(11) NOT NULL DEFAULT ‘0‘ COMMENT ‘评论数‘;

select count(1) as comment_num ,tips_id from tb_tips_comments GROUP BY tips_id ;

-- 创建临时表存储 所有tips 的统计数量
create temporary table statistical_comments(select count(1) as comment_num ,tips_id from tb_tips_comments GROUP BY tips_id);


-- 把评论表中有的数据但是tips表没有的数据清理掉
select tb_tips.tips_id ,statistical_comments.tips_id  from tb_tips right join statistical_comments on tb_tips.tips_id = statistical_comments.tips_id where  tb_tips.tips_id is null ;

delete FROM  tb_tips_comments where tips_id in (select statistical_comments.tips_id  from tb_tips right join statistical_comments on tb_tips.tips_id = statistical_comments.tips_id where  tb_tips.tips_id is null);
-- 把统计的评论数同步到tips表格
update tb_tips,statistical_comments set tb_tips.comment_num = statistical_comments.comment_num where tb_tips.tips_id = statistical_comments.tips_id;

-- 查看结果是否和预期一样。
select count(1) as comment_num ,tips_id from tb_tips_comments GROUP BY tips_id ORDER BY comment_num ,tips_id;
select comment_num,tips_id from tb_tips where comment_num >0 ORDER BY comment_num ,tips_id ;
    -- 用完释放
drop TEMPORARY TABLE statistical_comments;