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]
