Update SubSelect
Posted: Mon Feb 19, 2007 12:12 am
feyd | Please use
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?
returns "invalid use of group clause"
works, but
Runs constantly even when few rows match.[/syntax]
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)