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