Well, getting back only table one could be because you can only select * from one table at a time, I am not sure. If so you need to either spell out your columns specifically, or use a UNION in the query. As for the fail when there are no children, you need a LEFT JOIN.
http://www.mysql.com/doc/en/UNION.html http://www.mysql.com/doc/en/JOIN.html Mike Hillyer www.vbmysql.com -----Original Message----- From: Terry Spencer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:44 AM To: Mike Hillyer; MySQL (E-mail) Subject: RE: Update in select Thanks for the help, that close to what Im after. I however simplified the problem too much in my previous question and Im still not there. :) Im imitating a 'tree' structure. The table has rows - ID and parent_ID. The depth of the tree is only three layers (parent -> child -> grandchild). There may be any number of parents. Various solution exists to the tree problem that involve adding a additional row (or two) to the table. This is not an option available to me at this point. Under certain circumstances if the parent is updated, I also need to update the children and grandchildren. So far I have (using a select while testing :) select t1.*, t2.*, t3.* from tree t1, tree t2, tree t3 where t1.id = 10 and t2.parent_id = t1.id and t3.parent_id = t2.id However this only returns the parent row. If I instead use "select t2.*" the children are returned If I instead use "select t3.*" the grandchildren are returned So my problem is 1. why is only the result selected for the front table; t1 returns only parent, t2 return only the children 2. this fails for parent that have no children, and children that have no grandchildren. Any thought, suggestion, points are greatly appreciated. Im using 4.1. Thanks Terry -----Original Message----- From: Mike Hillyer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 2:53 PM To: Terry Spencer; [EMAIL PROTECTED] Subject: RE: Update in select Subqueries are only available in MySQL 4.1. However, you should be able to write this as follows: UPDATE test1 a, test2 b SET a.visit_date = NOW() WHERE a.id = b.id AND b.code = 'Z'; Regards, Mike Hillyer www.vbmysql.com -----Original Message----- From: Terry Spencer [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 7:26 AM To: [EMAIL PROTECTED] Subject: Update in select Im attempting to update a table. We perform a select on the table to determine what row to update. update test a set visit_date = now() where a.id in (select b.id from test b where code ='Z') Running this generates an error. "You cant specify target table 'test' for update in FROM clause." I can locate documentation on the constraints on referring to the target table in the FROM clause. Can anyone point me to any? In my example the rows the select is the select are not being updated. Is there anyways to perform this type of statement? Thanks. Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
