Subqueries are not supported until mysql 4.1. I'm guessing you have an
earlier version. With version 4.0.4 or later, you can accomplish the same
thing with a multi-table update:
UPDATE tbl1, tbl2 SET tbl1.col1 = tbl1.col1 + 1
WHERE tbl1.ID = tbl2.ID AND tbl2.status='Active';
or equivalently
UPDATE tbl1 JOIN tbl2 ON tbl1.ID = tbl2.ID
SET tbl1.col1 = tbl1.col1 + 1
WHERE tbl2.status='Active';
See the manual for more <http://dev.mysql.com/doc/mysql/en/UPDATE.html>.
Michael
prolist wrote:
I am trying to update a related table with a subquery. This is what I am
using -
update tbl1 set col1=col1+1 where ID IN (select ID from tbl2 where
status='Active');
But I get syntax error. I am not much of a database guy, so can't understand
what am I doing incorrectly. Can someone help?
TIA,
- Manish
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
