Recommended approach for UPDATE referencing same table?

[David Wynter]

Hi,

Have been converting lots of qeuries from a Servlet application using SQL Server. The inability for MySQL 4.1.11 to reference the same table being updated in a sub query is a real pain. We have a number of self referential tables. What is the recommended method of doing this type of query?

UPDATE tabl1 ce1, tabl1 ce2 SET ce1.Previous_Event_Id = ce2.Event_Id
WHERE ce2.Event_Id = (SELECT MAX(ce.Event_Id) FROM tabl1 ce
WHERE ce1.Div_Market_Lvl_Id = ce.Div_Market_Lvl_Id AND ce1.RIC = ce.RIC
AND ce1.Previous_Event_Id IS NULL)

Thx.

David