Szopen Xiao

MySQL使用SELECT结果集更新其他表

2013-11-01

假如有表A,有如下字段:

uid         用户ID
post_count  用户发贴数
is_del      用户是否已经被删除,0是否

表B有如下字段

uid         作者ID
post_id     帖子ID
is_del      帖子是否被删除,0是否

post_count 的值是 post_id 根据uidCOUNT值,现在需要重新校正post_count的值,使用如下SQL:

UPDATE A INNER JOIN (
SELECT count( B.post_id ) AS cnt, B.uid AS id
FROM A
LEFT JOIN B ON A.uid = B.uid
WHERE A.is_del =0 AND B.is_del = 0
GROUP BY B.id
) AS C ON A.uid = C.id
SET A.post_count = C.cnt
comments powered by Disqus