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 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
En esta página del sitio puede ver el video en línea SQL Tutorial - How to remove data using CTEs de Duración hora minuto segunda en buena calidad , que subió el usuario BeardedDev 12 mayo 2019, comparta el enlace con amigos y conocidos, en youtube este video ya ha sido visto 1,936 veces y le gustó 60 a los espectadores. Disfruta viendo!
