Bug #6969 Subqueries
Submitted: 2 Dec 2004 22:56 Modified: 3 Dec 2004 7:58
Reporter: Henry Cross Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[2 Dec 2004 22:56] Henry Cross 
Description:
The following SQL statement does not function correctly.

mysql> UPDATE tbl_paths WHERE path_id in (SELECT path_id FROM tbl_paths p WHERE NOT EXISTS (SELECT * FROM tbl_run_detail WHERE path_id=p.path_id AND file_type='1' AND run_id='2') AND EXISTS (SELECT * FROM tbl_run_detail WHERE path_id=p.path_id AND file_type='1' AND run_id='1'));

ERROR 1064 (42000): 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 'WHERE path_id in (SELECT path_id FROM tbl_paths p WHERE NOT EXISTS (SELECT * FRO' at line 1

I've tested on two different 4.1.7 servers with the same results.  Removing the outer UPDATE and running just the outermost query (with subselects) works perfectly.

How to repeat:
Any UPDATE with the same basic subquery setup will fail.

Suggested fix:
Interium - I'm putting the select results into a temporary table then doing the update using the temp table data.
[3 Dec 2004 7:58] MySQL Verification Team 
Hi,

Thank you for the report. 
First of all, please check correct syntax for UPDATE in the MySQL manual:
http://dev.mysql.com/doc/mysql/en/UPDATE.html

The second, currently you can't  update a table and select from the same table in a subquery.