subquery error: "You can't specify target table 'TABLE' for update in FROM clause"

[WebDevGuy]

TABLEA:
id
scu

DELETE FROM TABLEA
WHERE id NOT IN (
(
SELECT MAX(id)
FROM TABLEA
GROUP BY scu
)
)

Any ideas why it's throwing that error?

[Dan Grossman]

you cannot delete from a table and select from the same table in a subquery

Create a temporary table from the SELECT query and use that as the condition for your DELETE, then drop the temporary table.

[WebDevGuy]

thanks.

[chris_fuel]

According to a bug report (#5037) there is a workaround that's something like this:

This works in MYSQL version 5.0.20a-standard

DELETE FROM TABLE1 WHERE TABLEID IN
(
select TABLEID from (SELECT TABLEID FROM TABLE1 WHERE TABLEID = 2689891) AS
NEWTABLENAME
);

Unfortunately I'm not sure how that would compare performance wise against what Dan recommended, and it is somewhat version specific (unless that person only tested it on 5).

[WebDevGuy]

chris - most helpful - thanks very much