On 5/29/05, Lieven De Keyzer wrote: > From: Chris >> Lieven De Keyzer wrote: >>> >>> UPDATE account >>> SET role_id = (SELECT role_id FROM role WHERE rolename = "admin") >>> WHERE username = "test" >>> >>> This gives me an: >>> ERROR 1064 (00000): You have an error in your SQL syntax. Check the >>> manual that corresponds to your MySQL server version for the right syntax >>> to use near 'SELECT role_id from role WHERE rolename="admin")' at line 1 >> >> Syntax looks fine to me.
There is no guarantee that the subselect will return exactly one row. If there are ppoper constraints MySQL might get to the conclusion that thois subselect can not return more then one row, but how should it handle the case where it returns no row at all? >> Sub-queries are only supported in 4.1.x+ , I'm guessing that's your >> problem. > > I've got mysql-4.1.10 installed. Its too early to think joins on non-keys entirely through, but I think you are looking for something along the lines of: UPDATE account a, role r SET a.role_id = r.role_id WHERE a.user_name=admin AND r.rolename = admin; If not, use a variable. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
