Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
I want to update daily stats of the number of posts.
[syntax="sql"]UPDATE boardfstats AS s SET s.postCount = c WHERE s.stamp = st
(SELECT DATE( addDate ) AS st, COUNT(*) AS c FROM boardposts
GROUP BY DATE( addDate ))stamp (date)
postCount(number of posts on that day)
posts structure:
addDate (datetime)
I've seen exampes of returning a single variable from a SubSelect, but how can I use it both as the variable to update to, and to specify which record to update?
Code: Select all
UPDATE boardfstats_copy AS s, boardposts AS p SET s.postCount = COUNT(p.id) WHERE s.stamp = DATE(p.addDate)Code: Select all
UPDATE boardfstats_copy AS s SET s.postCount = (SELECT COUNT(*) AS c FROM boardposts WHERE DATE(addDate) = '2006-03-06') WHERE s.stamp = '2006-03-06'Code: Select all
UPDATE boardfstats_copy AS s SET s.postCount = (SELECT COUNT(*) AS c FROM boardposts WHERE DATE(addDate) = s.stamp)