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
