SQL Tutorial - How to remove data using CTEs

Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.
You can now support me on patreon - / beardeddev
In this SQL Tutorial we remove data from a table using a CTE. We go through examples of populating duplicate data in a table then identifying the row number then deleting from the CTE which in turn deletes from the underlying table. We also discuss that when using this method there can only be one underlying table in the CTE and show an error that will be shown when joins are involved.
If you would like to see more video tutorials on CTEs check out my playlist: • SQL TUTORIAL - CTEs Pa...
Code to follow tutorial:
-- check if Customers table exists
IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
DROP TABLE dbo.Customers
-- create table Customers
CREATE TABLE dbo.Customers
(
FirstName VARCHAR(50)
, LastName VARCHAR(50)
, DOB DATE
, CityId INT
)
-- check if City table exists
IF OBJECT_ID(N'dbo.City', N'U') IS NOT NULL
DROP TABLE dbo.City
-- create table City
CREATE TABLE dbo.City
(
CityId INT
, City VARCHAR(50)
)
-- populate City table
INSERT INTO dbo.City (CityId, City)
VALUES
(1, 'Birmingham')
, (2, 'London')
, (3, 'Manchester')
, (4, 'Newcastle')
-- simulate inserting duplicate data, each customer is inserted 3x
INSERT INTO dbo.Customers (FirstName, LastName, DOB, CityId)
VALUES
('Tony', 'Smith', '19840407', 1)
, ('Michelle', 'Carter', '19951122', 2)
, ('Sarah', 'Gulliver', '19790730', 3)
, ('Matthew', 'Wilkins', '19880808', 4)
, ('Tony', 'Smith', '19840407', 1)
, ('Michelle', 'Carter', '19951122', 2)
, ('Sarah', 'Gulliver', '19790730', 3)
, ('Matthew', 'Wilkins', '19880808', 4)
, ('Tony', 'Smith', '19840407', 1)
, ('Michelle', 'Carter', '19951122', 2)
, ('Sarah', 'Gulliver', '19790730', 3)
, ('Matthew', 'Wilkins', '19880808', 4)
WITH CTE
AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY FirstName, LastName, DOB, CityId ORDER BY FirstName) AS R
, *
FROM dbo.Customers
)
DELETE FROM CTE
WHERE R (insert greater than or equal to here) 2

[@TheCazz10]

love your videos mate, clear and concise and you speak slowly so i can follow along

[@BeardedDevData]

Thanks so much, let me know if there's anything in particular you want me to cover

[@houstonvanhoy7767]

You are right, Carolyn. Numerous content creators can't be followed or understood unless I reduce their speed to .75. 🙄

[@cromagnon201]

Wow, delete from temporary CTE table actually deletes it from the source table as well. Great if you are trying to remove duplicates. logicaly I would think it just deletes from the newly created temp table. Better be careful with that Delete command.

[@pervasivedoubt150]

Didn’t know this! Good video

[@oz7075]

Hello. So, the CTE does actually modify (INSERT, UPDATE, DELETE) the underlying tables.... Then, I guess that without permissions a Data Analyst wouldn't be able to run a CTE, right? Are CTEs meant for DBAs? What if we just want to run a query that leads to the same result as the CTE but without modifying the underlying table, how would we go about it, then?

[@oz7075]

Just dismiss my last question, it's obvious that we would have to run a SELECT and not a DELETE/UPDATE/INSERT. Still, I'm interested in the answers to the other questions. Thanks!

[@BeardedDevData]

It's actually the DML statement that modifies the data, in this example, DELETE is used, not the CTE itself. It's just that the Database Engine is intelligent enough to understand what your intention is. A Data Analyst or anybody with read permissions can create a CTE.

[@oz7075]

@@BeardedDevData Thanks a lot for your reply. It's much clearer now. Anyways, wouldn't be dangerous that a Data Analyst that is supposed to just run queries has the ability to perform permanent modifications to the database? I thought only SELECT was allowed for their profiles 🤔

[@BeardedDevData]

It depends on the company really, I've worked at places where Data Analysts can modify data but only in certain tables then at my current company, the Data Analysts only have read access. It's always best practice to implement the least privilege for people to carry out there roles.

[@oz7075]

@@BeardedDevData Awesome. Thanks for replying. I'm really enjoying your videos. The pace and examples are great and I think you do have a voice for commercials lol. Thanks again for putting this valuable content together, you rock 😎