Use a CTE to UPDATE or DELETE in MySQL

Since the CTE is not updatable, you need to refer to the original table to delete rows. I think you are looking for something like this:

WITH ToDelete AS (   SELECT ID,          ROW_NUMBER() OVER (PARTITION BY lastName, firstName ORDER BY ID) AS rn   FROM mytable)   DELETE FROM mytable USING mytable JOIN ToDelete ON mytable.ID = ToDelete.IDWHERE ToDelete.rn > 1; 

This appears to be a published bug in MySQL 8.x. From this bug report:

In the 2015 version of the SQL standard, a CTE cannot be defined in UPDATE; MySQL allows it but makes the CTE read-only (we're updating the documentation now to mention this). This said, one could use a view instead of the CTE; then the view may be updatable, but due to the presence of window functions it is materialized into a temporary table (it is not merged) so is not updatable (we're going to mention it in the doc as well).

All the above applies to DELETE too.

If you follow the above bug link, you will see a workaround suggested for using a CTE, but it involved joining the CTE to the original target table in a one-to-one mapping. Based on your example, which is a blanket delete, it is not clear what workaround you need, were to proceed using a CTE for your delete.