How do I update MySQL table, using subselect

how can 开发者_StackOverflowi write the query, to update the table videos, and set the value of field name to 'something' where the average is max(), or UPDATE the table, where average has the second value by size!!!

i think the query must look like this!!!

UPDATE videos 
   SET name = 'something' 
 WHERE average IN (SELECT `average`
                     FROM `videos`
                 ORDER BY `average` DESC
                    LIMIT 1)

but it doesn't work!!!

Two things here cause problems with my version of mysql (5.0.84) 1. Using limit not supported in subquery 2. Using table for update (videos) in subquery

I can't think of a good way to get around these problems. I'd suggest pulling the ids of hte rows you want to update out into your code and then executing the update in a second statement. If you are using pure sql and doing this by hand then you could always just select into a temp table and then update based on the ids that you insert there.

UPDATE videos 
   SET name = 'something' 
 WHERE videos.id IN (SELECT id
                     FROM `videos`
                 ORDER BY `average` DESC
                    LIMIT 1)