MySQL updating the same! table with subquery

Question

I wish to update some rows of the same table. As I know the following form 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

possible duplicate of [MYSQL Update with WHERE SELECT subquery error](http://stackoverflow.com/questions/6944165/mysql-update-with-where-select-subquery-error) — Marc B, Oct 06 '11 at 23:19

score 7 · Accepted Answer · answered Oct 06 '11 at 23:20

7

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 )

answered Oct 06 '11 at 23:20

zerkms

1 Answers1