Workaround in DELETE Query

Question

I am trying to delete all records that are not the latest version under their name, but apparently you cannot reference access to the table that you are editing in the same query.

I tried this, but it does not 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?

Greetings

Answer 1

Wrap the internal link in the view .

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)  

Answer 2

delete t1
from  table_name1 t1, table_name1 t2 
where t1.version < t2.version 
and t1.name = t2.name;

// creating an alias is a necessity