List:General Discussion« Previous MessageNext Message »
From:mos Date:November 9 2009 3:40pm
Subject:RE: Finding users who haven't posted in a week
View as plain text  
At 12:13 PM 11/8/2009, John Meyer wrote:
>Now I'm wondering if I can use this query in an update to set a variable in
>a second table
>
>Users
>------
>User_id VARCHAR(50)
>. . .
>User_active BITINT(1)
>
>I want to set user_active to 0 where the user_id is in the query below.
>
>select user_id, max(tweet_createdat) from tweets group by user_id having
>datediff(now(),max(tweet_createdat)) > 7;


You can try something like this:

update table2 set Slackers='Y' where user_id in (select user_id from tweets 
group by user_id having
datediff(now(),max(tweet_createdat)) > 7);

Mike



>-----Original Message-----
>From: John Meyer [mailto:johnmeyer@stripped]
>Sent: Sunday, November 08, 2009 9:45 AM
>To: 'Michael Dykman'
>Cc: mysql@stripped
>Subject: RE: Finding users who haven't posted in a week
>
>Thanks, morning coffee hasn't kicked in.  This worked out well.
>
>select user_id, max(tweet_createdat) from tweets group by user_id having
>datediff(now(),max(tweet_createdat)) > 7;
>
>I forgot when to use the where and when to use the having clause.
>
>-----Original Message-----
>From: Michael Dykman [mailto:mdykman@stripped]
>Sent: Sunday, November 08, 2009 8:35 AM
>To: John Meyer
>Cc: mysql@stripped
>Subject: Re: Finding users who haven't posted in a week
>
>the function max(), among others, makes no sense in the absence of a
>GROUP BY clause.
>
>try adding "GROUP BY user_id"
>
>  - michael dykman
>
>On Sun, Nov 8, 2009 at 9:40 AM, John Meyer
><johnmeyer@stripped> wrote:
> > I want to get a list of all users who haven't posted in a week. But when I
> > use the following function.
> >
> >
> >
> > select user_id, max(tweet_createdat) from tweets where
> > datediff(now(),max(tweet_createdat)) > 7;
> >
> >
> >
> >
> >
> > Is producing the error:
> >
> >
> >
> > Invalid use of group function
> >
> >
> >
> >
> >
> >
>
>
>
>--
>  - michael dykman
>  - mdykman@stripped
>
>"May you live every day of your life."
>     Jonathan Swift
>
>Larry's First Law of Language Redesign: Everyone wants the colon.
>No virus found in this incoming message.
>Checked by AVG - www.avg.com
>Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
>07:37:00
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>No virus found in this incoming message.
>Checked by AVG - www.avg.com
>Version: 8.5.425 / Virus Database: 270.14.52/2484 - Release Date: 11/08/09
>07:37:00
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Finding users who haven't posted in a weekJohn Meyer8 Nov
  • Re: Finding users who haven't posted in a weekMichael Dykman8 Nov
    • RE: Finding users who haven't posted in a weekJohn Meyer8 Nov
      • RE: Finding users who haven't posted in a weekJohn Meyer8 Nov
        • RE: Finding users who haven't posted in a weekmos9 Nov
          • Re: Finding users who haven't posted in a weekAnanda Kumar10 Nov
          • Re: Finding users who haven't posted in a weekJohnny Withers10 Nov