How to solve "ER_UPDATE_TABLE_USED" error in this code?

August 8, 2024

When I run this code I get the error:

ER_UPDATE_TABLE_USED: You can’t specify target table ‘Company’ for
update in FROM clause

DELETE FROM Company
WHERE name IN (
   SELECT name FROM Company as a
   JOIN Trip ON Trip.company = a.id
   GROUP BY a.name
   HAVING COUNT(Trip.id) = (
       SELECT COUNT(Trip.id) FROM Trip
       JOIN Company as b ON b.id = Trip.company
       GROUP BY b.name
       ORDER BY COUNT(Trip.id) ASC LIMIT 1
    )
)

I have encountered the "ER_UPDATE_TABLE_USED" problem before, but for some reason this time it just won’t go away. I submitted the "Company" table with a different name, but the error still appeared. What’s wrong?

>Solution :

You’re trying to delete from a table and simultaneously using that same table in a subquery with the WHERE clause.
MySQL still treats it as the same table, causing this error.

You should create a derived or temporary table first, then delete it.