06:12

	Content	Login

SQL Problems and solutions S. I. Moiseenko

Content

SQL console

back go forward

New sql features

Common table expressions (CTE) page 3

The query we have used in SQL Server -

WITH CTE AS (
    SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk
    FROM T
     )
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
('John'),
('Smith'),
('John'),
('Smith'),
('Smith'),
('Tom');

Here the query is

WITH t_deleted AS
(DELETE FROM T returning *), -- 1st step
t_inserted AS
(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)