SQL Tutorial - How to remove data using CTEs

Veröffentlicht am: 12 Mai 2019
auf dem Kanal: BeardedDev

1,936

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 Part 1

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

Auf dieser Seite können Sie das Online-Video SQL Tutorial - How to remove data using CTEs mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer BeardedDev 12 Mai 2019 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 1,936 Mal angesehen und es wurde von 60 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!

1,090