Why Can't I Update a MySQL Table Using a Subquery Referencing the Same Table in the FROM Clause?

The problem that the FROM clause in the MySQL UPDATE statement refers to the same table

Scene:

Consider a MySQL table named pers:

<code class="language-sql">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);</code>

Question:

Attempting to execute the following UPDATE statement results in error 1093:

<code class="language-sql">UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE (P.chefID IS NOT NULL 
OR gehalt < 1000);</code>

Reason:

This error occurs because MySQL does not allow reference to the target table from within a subquery in UPDATE/INSERT/DELETE queries.

Solution:

To solve this problem, the subquery needs to be rewritten to explicitly copy the necessary fields in the target table to the temporary table. This can be achieved by replacing the instance of the target table with (SELECT * FROM target_table). However, the subquery is not needed in this example and can be modified directly.

Correct query:

<code class="language-sql">UPDATE pers P 
SET P.gehalt = P.gehalt * 1.05 
WHERE (P.chefID IS NOT NULL 
OR P.gehalt < 1000);</code>

This modified query uses WHERE directly in the P.gehalt clause, avoiding MySQL limitations and thus correctly updating the pers table. No need to create temporary tables.

The above is the detailed content of Why Can't I Update a MySQL Table Using a Subquery Referencing the Same Table in the FROM Clause?. For more information, please follow other related articles on the PHP Chinese website!