Why Does MySQL Throw Error 1093 When Updating a Table Using a Subquery?

MySQL Error 1093: Target Table Update in Subquery

When attempting to execute the following query in MySQL:

UPDATE giveaways SET winner = '1' WHERE ID = (SELECT MAX(ID) FROM giveaways)

You may encounter the following error:

#1093 - You can't specify target table 'giveaways' for update in FROM clause

This error occurs because MySQL does not allow the target table of an UPDATE statement (giveaways) to be used in a subquery within the WHERE clause.

Adapting the Solution from the Reference Article

To resolve this issue, adapt the solution provided in the article you referenced by using a nested subquery. Here is the updated query:

UPDATE giveaways SET winner='1'
WHERE Id = (SELECT Id FROM (SELECT MAX(Id) AS id FROM giveaways) AS t)

This query effectively selects the maximum Id value from the giveaways table as a subquery (t) and then uses that value to update the winner column in the outer query.

By nesting the subquery, you avoid specifying the target table (giveaways) in the FROM clause of the subquery, thus resolving the error.

The above is the detailed content of Why Does MySQL Throw Error 1093 When Updating a Table Using a Subquery?. For more information, please follow other related articles on the PHP Chinese website!