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