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 <mo...@fastmail.fm> 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: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=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
