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:johnme...@pueblocomputing.com]
Sent: Sunday, November 08, 2009 9:45 AM
To: 'Michael Dykman'
Cc: mysql@lists.mysql.com
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:mdyk...@gmail.com]
Sent: Sunday, November 08, 2009 8:35 AM
To: John Meyer
Cc: mysql@lists.mysql.com
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
<johnme...@pueblocomputing.com> 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
- mdyk...@gmail.com
"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=john.l.me...@gmail.com
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=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
