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; 

Answer 2

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.