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 - https://www.patreon.com/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: https://www.youtube.com/watch?v=3QuYuU9OJfA&list=PLgR-BOYibnN1jq0eQtCztDmNN6Af5RY-G

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

Видео SQL Tutorial - How to remove data using CTEs канала BeardedDev