MySQL error code 1093 when trying to delete rows

user_12950233 0
Viewed 93

I'm doing a homework assignment where the instructions are "Delete all players who live in the same town as player 57 but keep the data about player 57." There is a table called "Players" that has PlayerNo and Town as columns well as various other data. I tried the following query:

delete from players 
where town = (select town from players where playerno = 57) 
and playerno <> 57;

But am receiving the following error message:

"Error Code: 1093. You can't specify target table 'players' for update in FROM clause"

How do I fix this?

mysql
2 Answers

You may rephrase your delete query using an inner join:

DELETE
FROM players p1
INNER JOIN (SELECT town FROM players WHERE playerno = 57) p2
    ON p2.town = p1.town
WHERE
    p1.playerno <> 57;

By the way, the reason for the 1093 error is that MySQL might be in the process of modifying the players table at the same time your subquery is attempting to reference it. One workaround is to wrap that subquery in another subquery:

DELETE
FROM players
WHERE town = (SELECT town
              FROM (SELECT town
                    FROM players
                    WHERE playerno = 57) t ) AND
      playerno <> 57;

This trick works because MySQL will first materialize the subquery before the update runs, to avoid the 1093 error.

user_18632290

You can resolve the problem by simple query:

delete players.*
from players 
join players players_town on 
    players.town = players_town.town and 
    players_town.playerno = 57 
where players.playerno <> 57;

Test SQL here

user_33993560
Related