MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Mike Blezien Date:July 24 2004 5:08pm
Subject:Re: Query Possible
View as plain text  
Michael Stassen wrote:
> First, I think the LEFT JOIN is unnecessary.  Rows in members without a 
> corresponding row in active will not have acct_days>0 (it'll be NULL), 
> so, by using LEFT JOIN you are (possibly) creating rows only to discard 
> them.  I think a simple join will do.
> 
> You want a multi-table update:
> 
>   UPDATE members JOIN active
>   ON active.memid = members.memid
>   SET mmembers.account = 2
>   WHERE active.acct_days > 0 AND members.account = 1;
> 
> This works starting with 4.0.4, so it will work in your 4.0.20.  See the 
> manual <http://dev.mysql.com/doc/mysql/en/UPDATE.html> for more.
> 
> Michael
> 
> Mike Blezien wrote:
> 
>> Hello,
>>
>> MySQL version 4.0.20
>>
>> I'm trying to figure out, if it's possible to do, with one query to 
>> update a single table.
>>
>> Query #1
>> select m.memid from members m left join active a
>> on a.memid = m.memid where a.acct_days > 0 and m.account = 1;
>>
>> Query # 2
>> update members set account = 2 where memid in (results from query #1)
>>
>> Can this all be done in one sql statement, in query 2?


Thx's appreciate the info. This should do the trick :)

-- 
Mike<mickalo>Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Thread
Query PossibleMike Blezien24 Jul
  • Re: Query PossibleMichael Stassen24 Jul
  • Re: Query PossibleMike Blezien24 Jul