Using CTE with DELETE


mpskovvang [5:02 AM]
From the documentation I would expect the WITH to work with DELETE, but the following SQL query returns “Table ‘test.cte’ doesn’t exists”:

with cte as (select row_number() over (partition by id) as duplicates from pages) delete from cte where duplicates > 1;

WITH clauses are supported as part of SELECT, INSERT, UPDATE, DELETE, and REPLACE statements.

robbie [9:37 AM]
The cte itself is not writeable but can be joined against and used elsewhere in a delete

mpskovvang [10:32 AM]
Thanks @robbie for pointing that out. So the query has to be rewritten to something like WITH cte AS (...) DELETE FROM table JOIN cte ON .... WHERE cte.duplicates > 1;, right?

robbie [19 days ago]
yea something like that could work. You’d want to add another condition to join table table and cte together


Already answered by Robbie!