You can't specify target table for update in FROM clause

Question

I have a simple mysql table:

CREATE TABLE IF NOT EXISTS `pers` (   `persID` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(35) NOT NULL,   `gehalt` int(11) NOT NULL,   `chefID` int(11) DEFAULT NULL,   PRIMARY KEY (`persID`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;  INSERT INTO `pers` (`persID`, `name`, `gehalt`, `chefID`) VALUES (1, 'blb', 1000, 3), (2, 'as', 1000, 3), (3, 'chef', 1040, NULL); 

I tried to run following update, but I get only the error 1093:

UPDATE pers P  SET P.gehalt = P.gehalt * 1.05  WHERE (P.chefID IS NOT NULL  OR gehalt <  (SELECT (     SELECT MAX(gehalt * 1.05)      FROM pers MA      WHERE MA.chefID = MA.chefID)      AS _pers )) 

I searched for the error and found from mysql following page http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html, but it doesn't help me.

What shall I do to correct the sql query?

Answer 1

The problem is that MySQL, for whatever inane reason, doesn't allow you to write queries like this:

UPDATE myTable SET myTable.A = (     SELECT B     FROM myTable     INNER JOIN ... ) 

That is, if you're doing an UPDATE/INSERT/DELETE on a table, you can't reference that table in an inner query (you can however reference a field from that outer table...)

---

The solution is to replace the instance of myTable in the sub-query with (SELECT * FROM myTable), like this

UPDATE myTable SET myTable.A = (     SELECT B     FROM (SELECT * FROM myTable) AS something     INNER JOIN ... ) 

This apparently causes the necessary fields to be implicitly copied into a temporary table, so it's allowed.

I found this solution here. A note from that article:

You don’t want to just SELECT * FROM table in the subquery in real life; I just wanted to keep the examples simple. In reality, you should only be selecting the columns you need in that innermost query, and adding a good WHERE clause to limit the results, too.