"Diana Castillo" <[EMAIL PROTECTED]> wrote on 01/28/2005 11:53:20 AM:
> how do I make a query that does this? > update tbl_a set location_code=0 where tbl_a.country_id = (select id from > countries where has_zones=0) > The UPDATE statement (as of 4.0.4) allows you to update multiple tables at once. (All of the JOIN methods are supposed to be valid but you cannot use ORDER BY or LIMIT with a multitable update) http://dev.mysql.com/doc/mysql/en/update.html So, if we wrote a query just to "look" at the rows you want to UPDATE (without using a subselect), you could write: SELECT * FROM tbl_a INNER JOIN countries ON tbl_a.country_id = countries.id AND countries.has_zones = 0; To translate that into an UPDATE statement, all we need to do is a little re-arranging: UPDATE tbl_a INNER JOIN countries ON tbl_a.country_id = countries.id AND countries.has_zones = 0 SET location_code=0; The "FROM" tables become the "UPDATE" tables. If we had a where clause, it remains the same. Here is another way to write the same SELECT statement: SELECT * FROM tbl_a INNER JOIN countries ON tbl_a.country_id = countries.id WHERE countries.has_zones = 0; And this would be the alternative UPDATE: UPDATE tbl_a INNER JOIN countries ON tbl_a.country_id = countries.id SET location_code=0 WHERE countries.has_zones = 0; If this doesn't work for you (old version?) write back and we can work up something else that will. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
