List:General Discussion« Previous MessageNext Message »
From:Ananda Kumar Date:November 10 2009 10:33am
Subject:Re: Finding users who haven't posted in a week
View as plain text  
make sure u have a "WHERE CLAUSE" for the update , otherwise, entire data
for that column will be seto "Y"

On Mon, Nov 9, 2009 at 9:10 PM, 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
>
>

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