Bug #33294 Multiple Table UPDATE not working as expected.
Submitted: 17 Dec 2007 17:33 Modified: 17 Dec 2007 18:33
Reporter: Charles Alcorn Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Linux
Assigned to: CPU Architecture:Any

[17 Dec 2007 17:33] Charles Alcorn 
Description:
Multiple table UPDATE behaves differently when joining a table onto itself.  

If you join the table onto itself using an alias the results do not agree with what one expects.  It seems that the join criteria is also being used to limit which records are updated.

If, on the other hand, you make a copy of the table and use the copy in the multiple table UPDATE the results are as expected.

In MySQL 5.0.45 both versions of this UPDATE work as expected.  However, I could not find a bug report that refers to this problem.

How to repeat:
See the attached file.
[17 Dec 2007 17:36] Charles Alcorn 
SQL queries which illustrate the problem.

Attachment: SQL_UPDATE_Bug.sql (text/x-sql), 3.05 KiB.
[17 Dec 2007 18:33] Susanne Ebrecht 
Thank you for taking the time to report a problem.  
We don't support MySQL version 4.1 anymore (besides critical security bugs).
You can try to upgrade to 4.1.22 (last released version).
As you write, this error don't occur in MySQL 5.0.
Please use our newest 5.0 version.

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.