Updating with sub query on the same table

I was trying to ach开发者_如何学JAVAieve this query1:

UPDATE temp_svn1 t set closedate=(select max(date) from temp_svn1 p where p.id=t.id

Apparently MySQL does not allow such queries. So I came up with this query using inner joins but this is too slow. How can I write a better query for this? OR How can I achieve the logic of query 1?

UPDATE temp_svn1 AS out INNER JOIN (select id, close from temp_svn1 T inner join (select id as cat, max(date) as close from temp_svn1 group by cat) as in where T.id = in.cat group by id ) as result ON out.id = result.id SET out.closedate = result.close

Because of mysql peculiarities the only way you're going to get this to be faster is to split it into two statements. First, fetch the max, and then use it in an update. This is a known hack, and, afaik, there's not a "neater" way of doing it in a single statement.

Since your subquery is just returning a single value, you could do the query in two stages. Select the max(date) into a server-side variable, then re-use that variable in the outer query. Of course, this breaks things up into two queries and it'll no longer be atomic. But with appropriate transactions/locks, that becomes moot.

This works:

UPDATE temp_svn1
set closedate = (select max(date) from temp_svn1 p where p.id = temp_svn1.id)