Year: 2022

[SQL] Removing duplicates

Agnieszka

I created two simple tables connected with each other with one key in database, which I called Test1

DATABASE TEST1

Create database Test1

Use Test1

TABLE NAMES

create table NAMES
(
PersonID bigint primary key not null,
PersonLastName Varchar(100) not null,
PersonFirstName Varchar(80) not null,
PersonBirthDate Date not null,
)

TABLE ADDRESSES

create table ADDRESSES
(
Country Varchar(30) not null,
City Varchar(50) not null,
Street Varchar(100) not null,
Number Varchar (10) not null,
ZipCode Varchar(6) not null,
AddID bigint references dbo.NAMES(PersonID)
)

Made some inserts. For Names I could use ID number combination only once as its primary key with an unique value, but accidentaly I added some dublicates for same ID number in table Addreses. Searching for simple resolution to remove that extra lines and found this method:

WITH CTE AS(
   SELECT Country, City, Street, Number, ZipCode, AddID,
       RowNum = ROW_NUMBER()OVER(PARTITION BY AddID ORDER BY AddID)
   FROM dbo.ADDRESSES
)
DELETE FROM CTE WHERE RowNum > 1

Enjoy!