loading..
Русский    English
08:48

Common table expressions (CTE) page 3

The query we have used in SQL Server -

  1. WITH CTE AS (
  2.     SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk
  3.     FROM T
  4.      )
  5. DELETE FROM CTE
  6. 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:

  1. Delete all rows from the base table, returning them into common table expression (first CTE).
  2. Using the result obtained in the 1st step, form unique rows which we need to retain in the table (second CTE).
  3. Insert the rows from the step 2 into the base table.

Let's use the table from cited example for writing the query:

  1. CREATE TABLE T (name varchar(10));
  2. INSERT INTO T VALUES
  3. ('John'),
  4. ('Smith'),
  5. ('John'),
  6. ('Smith'),
  7. ('Smith'),
  8. ('Tom');

Here the query is

  1. WITH t_deleted AS
  2. (DELETE FROM T returning *), -- 1st step
  3. t_inserted AS
  4. (SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk
  5.     FROM t_deleted) -- 2nd step
  6. INSERT INTO T SELECT name FROM t_inserted
  7. WHERE rnk=1; -- 3rd step
  8. --(here we move the search condition from the step 2 to reduce the code)

When we run the query

  1. SELECT * FROM T;

the desired result will be obtained:

name
John
Smith
Tom

Bookmark and Share
Pages 1 2 3
Развернуть всё
Свернуть всё

Content:

Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.