The target table for updates cannot be specified in the 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 running the following update but only received 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 the following page from mysql http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html but that didn't help me.

How do I correct the sql query?

Answer 1

You can do this in three steps:

CREATE TABLE test2 AS
SELECT PersId 
FROM pers p
WHERE (
  chefID IS NOT NULL 
  OR gehalt < (
    SELECT MAX (
      gehalt * 1.05
    )
    FROM pers MA
    WHERE MA.chefID = p.chefID
  )
)

...

UPDATE pers P
SET P.gehalt = P.gehalt * 1.05
WHERE PersId
IN (
  SELECT PersId
  FROM test2
)
DROP TABLE test2;

or

UPDATE Pers P, (
  SELECT PersId
  FROM pers p
  WHERE (
   chefID IS NOT NULL 
   OR gehalt < (
     SELECT MAX (
       gehalt * 1.05
     )
     FROM pers MA
     WHERE MA.chefID = p.chefID
   )
 )
) t
SET P.gehalt = P.gehalt * 1.05
WHERE p.PersId = t.PersId

Answer 2

The problem is that, for whatever stupid reason, MySQL doesn't let you write a query like this:

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

That is, if you want to perform UPDATE/INSERT/DELETE operations on the table, you cannot query internally (but you Can reference fields in external tables...)

---

The solution is to replace the myTable instance in the subquery with (SELECT * FROM myTable) as shown below

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

This will obviously cause the necessary fields to be implicitly copied into the temporary table, so this is allowed.

I found this solution here. Notes for this article: