Get around self-referencing in a DELETE query

Question

I'm trying to delete all records which aren't the latest version under their name but apparently you can't reference access a table you are modifying in the same query.

I tried this but it doesn't work for the reasons above:

DELETE FROM table
WHERE CONCAT(name, version ) NOT IN (
SELECT CONCAT( name, MAX( version ) )
FROM table
GROUP name
)

How can I get around this?

Cheers

Answer 1

Wrap the inner reference in a derived table.

DELETE FROM table
WHERE  Concat(name, version) NOT IN (SELECT nv
                                     FROM   (SELECT Concat(name, Max(version))
                                                    AS nv
                                             FROM   table
                                             GROUP  BY name) AS derived)