List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:November 10 2009 1:27pm
Subject:Re: Finding users who haven't posted in a week
View as plain text  
Update tweets, t2
set t2.active=0
where tweets.user_id=t2.user_id
and (rest of tweets where cond)

I'm on a mobile device, but that query should work, just do not leave
the tweets=t2 cond out of the where.


On Monday, November 9, 2009, mos <mos99@stripped> wrote:
> 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
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>

-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped
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