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 <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=anan...@gmail.com > >
