Common Table Expression (CTE)
A Common Table Expression (CTE) is the result set of a query which exists temporarily and for use only within the context of a larger query. You can use this functionality to query more complex queries and hierarchal data. One reason I often use is to remove duplicates, which is the example below. Do delete the rows vs select, you change the “SELECT *” to “DELETE”.
Every CTE starts with a “WITH” command. What comes after the “WITH” is your temporary table name.
The overall format of a CTE:
WITH [TABLE_NAME]
AS
(
QUERY
)
SELECT *
FROM [TABLE_NAME]
At the point after the “)” you can use all the normal commands of a query like SELECT, DELETE, UPDATE, etc.
Below is an example script to see how CTE can be used to remove duplicates.
--Create a table for our example. Just includes the state and city.
CREATE TABLE CITIES
(
ST NVARCHAR(50),
CITY NVARCHAR(50)
--Insert into the new table a few records, 1 being a duplicate.
INSERT INTO CITIES
SELECT 'Illinois' AS ST, 'Chicago' AS CITY
UNION ALL SELECT 'Illinois' AS ST, 'Chicago' AS CITY
UNION ALL SELECT 'Illinois' AS ST, 'Springfield' AS CITY
UNION ALL SELECT 'Wisconsin' AS ST, 'Madison' AS CITY
--Select all the records from the new table. Rows 1 and 2 are duplicates.
SELECT *
FROM CITIES
WITH FindDuplicates
AS
( --Add in a Row Number by state and city. If there is a duplicate, then 1,2. For non-duplicates = 1.
SELECT ROW_NUMBER() OVER(PARTITION BY ST, CITY ORDER BY CITY ASC) RowNumber,*
FROM
(
--Select everything from the new table.
SELECT *
FROM CITIES
) X
)--Delete any Duplicates.
DELETE
FROM FindDuplicates
WHERE RowNumber > 1
--Select everything from table created and you can see the duplicate record is no longer there.
SELECT *
FROM CITIES