CTE delete my data in my source table, but why?-CodePudding

I found this weird script and can not understand it. Why this cte delete data in my table sampledata? I never wrote a command to do so? Can someone understand it?

DROP TABLE IF EXISTS #SampleData;
GO

CREATE TABLE #SampleData
(
IntegerValue INTEGER,
);
GO

INSERT INTO #SampleData VALUES
(1),(1),(2),(3),(3),(4);
GO

WITH cte_Duplicates AS
(
SELECT  ROW_NUMBER() OVER (PARTITION BY IntegerValue ORDER BY IntegerValue) AS Rnk
FROM    #SampleData
)
DELETE FROM cte_Duplicates WHERE Rnk > 1
GO

Try it out: https://dbfiddle.uk/M63U8OYt

CodePudding user response：

A CTE still operates on its underlying table(s).

Its purpose is to present or operate on the data in a better readable form, especially when writing complex queries.

Its purpose is not to create a copy of the table(s), so the tables are not changed when executing delete, update or insert commands.

Therefore, your command

DELETE FROM cte_Duplicates WHERE Rnk > 1

removes the duplicates from your sample table.

If you want to apply changes on a copy of your table instead of the table itself, a CTE is not suffiient.

In this case, you need a temp table.

See also here the differences between CTE's and temp tables: differences