Bug #4245 Update Query
Submitted: 22 Jun 2004 9:18 Modified: 23 Jun 2004 1:12
Reporter: Manyao Nico Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5 OS:Windows (Windows)
Assigned to: Assigned Account CPU Architecture:Any

[22 Jun 2004 9:18] Manyao Nico 
Description:
I ran the following Update Query: 
Update Test a
Set a.Cancelflag=-1
where a.eventid=142
and a.customernumber in
(select b.customernumber from Test b 
where b.eventid=133 and b.productid=a.productid)

This Query joins the test table against itself for update. Seems MySql does not support self joins for Update. Is there a way around this problem?

How to repeat:
SQL UPDATE Locking for self joins

Suggested fix:
Make temporary table copy and join to it.
[23 Jun 2004 0:06] Matthew Lord 
Hi,

Could you provide me with your table structure using show create table?  This will make it quick and 
easy for me to validate this in 4.1.2.  Hopefully this has been addressed since 5.0.0 was released but I 
will see.

Best Regards,
[23 Jun 2004 1:12] Matthew Lord 
Hi Manyao,

This is a duplicate of 3327.  There is currently an implementation limit on this type of query.  It is on 
the todo list to remove the restriction.

Best Regards