Error Code: 1288. The target table cte of the DELETE is not updatable

Question

How can I remove duplicates from below table from clients table?

Below SELECT query is giving me correct results however delete query is giving error.

WITH cte AS (
    SELECT ROW_NUMBER () OVER (PARTITION BY id) AS row_num, id, first_name, last_name
    FROM clients
)
SELECT * FROM cte

But:

WITH cte AS (
    SELECT ROW_NUMBER () OVER (PARTITION BY id) AS row_num, id, first_name, last_name
    FROM clients
)
DELETE FROM cte
WHERE row_num > 1;

Gives error:

Error Code: 1288. The target table cte of the DELETE is not updatable.

I would like to understand why I am getting above error (1288). Thanks.

Expected result:

Answer 1

Answer based on previous answer which I think this is a duplicate as well.

The answer would be to use a primary key or:

• create a new table based on the current one

create table clients_distinct like clients

• add only the unique ids

INSERT INTO clients_distinct 
    SELECT DISTINCT * FROM clients;

• drop the original table

drop table clients

• rename the table

rename table clients_distinct to clients

fiddle