Use a CTE to UPDATE or DELETE in MySQL

Question

The new version of MySQL, 8.0, now supports Common Table Expressions.

According to the manual:

A WITH clause is permitted at the beginning of SELECT, UPDATE, and DELETE statements:

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

So, I thought, given the following table:

ID lastName firstName
----------------------
1  Smith    Pat
2  Smith    Pat
3  Smith    Bob

I can use the following query:

;WITH ToDelete AS 
(
   SELECT ID,
          ROW_NUMBER() OVER (PARTITION BY lastName, firstName ORDER BY ID) AS rn
   FROM mytable
)   
DELETE FROM ToDelete

in order to delete duplicates from the table, just like I could do in SQL Server.

It turns out I was wrong. When I try to execute the DELETE stament from MySQL Workbench I get the error:

Error Code: 1146. Table 'todelete' doesn't exist

I also get an error message when I try to do an UPDATE using the CTE.

So, my question is, how could one use a WITH clause in the context of an UPDATE or DELETE statement in MySQL (as cited in the manual of version 8.0)?

Answer 1

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.ID
WHERE ToDelete.rn > 1;