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
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]