The query we have used in SQL Server -
WITH CTE AS (
SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk
DELETE FROM CTE
WHERE rnk > 1;
will fail with error:
Relation "cte" does not exist.
This error means that we cannot delete rows from common table expressions (CTE), but only from base tables.
Nevertheless, it is possible to delete duplicates with a single query using CTE.
To do it, let's try the following algorithm:
- Delete all rows from the base table, returning them into common table expression (first CTE).
- Using the result obtained in the 1st step, form unique rows which we need to retain in the table (second CTE).
- Insert the rows from the step 2 into the base table.
Let's use the table from cited example for writing the query:
CREATE TABLE T (name varchar(10));
INSERT INTO T VALUES
Here the query is
WITH t_deleted AS
(DELETE FROM T returning *), -- 1st step
(SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk
FROM t_deleted) -- 2nd step
INSERT INTO T SELECT name FROM t_inserted
WHERE rnk=1; -- 3rd step
--(here we move the search condition from the step 2 to reduce the code)
When we run the query
the desired result will be obtained: