MySQL updating the same! table with subquery

I wish to update some rows of the same table. As I know the following f开发者_Python百科orm doesnt work under MySQL

 UPDATE footbl SET foocol=something WHERE foocol in (SELECT ft.foocol ... bla bla )

One post of MySQL forum suggests that: use subquery.

So my solution is:

 SELECT @data:=f2.fname ... bla bla
 UPDATE tfile2 SET fstatus='deleted' WHERE tfile2.fname=(@data);

But, unfortunatelly, if the subquery @data consits more than one row than only one row is updated in my case. !check this pic!

So, what did i wrong, how can update more than one row on the same table ?

Thanks for the effort and your time in advance.

Csaba

You can use the same table using double nesting. In this case mysql just cannot get that you're updating the same table:

UPDATE footbl
   SET foocol=something
 WHERE foocol in (SELECT foocool FROM (SELECT ft.foocol FROM footbl ...) x )