Page 1 of 1

Update SubSelect

Posted: Mon Feb 19, 2007 12:12 am
by GameMusic
feyd | Please use

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 ))
fstats structure:

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)
returns "invalid use of group clause"

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'
works, but

Code: Select all

UPDATE boardfstats_copy AS s SET s.postCount = (SELECT COUNT(*) AS c FROM boardposts WHERE DATE(addDate) = s.stamp)
Runs constantly even when few rows match.[/syntax]