CODEWITHSUNDEEP
mysqlsubquery
cscsaba
cscsaba

Reputation: 1289

MySQL updating the same! table with subquery

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

Upvotes: 1

Views: 2446

Answers (1)

zerkms
zerkms

Reputation: 255025

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 )

Upvotes: 7

Related Questions