How to delete duplicate rows in a SQL table | Delete and Avoid duplicate entries in SQL | Ms SQL
One or more rows that have identical or same data values are considered to be Duplicate rows.
Watch this video to delete duplicate entries in your SQL table and also how to aviod duplicate entries in a table
Below are the scripts that are used in this video.
-------------------------------------------
create table Table1(id int,name varchar(10))
insert into Table1 values ('101','Prasad')
insert into Table1 values ('101','Prasad')
insert into Table1 values ('101','Prasad')
insert into Table1 values ('102','Keerthi')
insert into Table1 values ('102','Keerthi')
insert into Table1 values ('103','David')
insert into Table1 values ('103','David')
select * from Table1
---To identify duplicate entries
select *,count(*) from Table1
group by name, id
having count(*) :- 1
--To delete duplicate entries
--Method 1 : create and drop another table
select distinct * from table1
select distinct * into temp from Table1
Truncate table table1
delete from Table1
insert into Table1 select * from temp
drop table temp
select * from table1
--Method 2 : Auto_id
alter table Table1
add Auto_id int identity(1,1)
select * from table1
delete from Table1
where auto_id not in (select min(auto_id) from Table1 group by name,id)
alter table Table1
drop column Auto_id
---Method 3 : Using CTE -- CTE allows you to define a temporary named result set that is available temporarily in the execution scope of a statement such as SELECT, INSERT, UPDATE, DELETE, or MERGE.
with EmpCte as
(
select *, row_number() over(partition by id order by id) as rowno from Table1
)
delete from EmpCte where rowno :- 1
select * from Table1
Use the primary keys, constraint keys, identity columns, clustered and non-clustered indexes to eliminate the possibility of duplicate rows.
--Aviod duplicate entries
select * from employee
alter table employee
add constraint unique_email unique(email)
alter table employee
add constraint Pkey primary key(ID)
Auf dieser Seite können Sie das Online-Video How to delete duplicate rows in a SQL table | Delete and Avoid duplicate entries in SQL | Ms SQL mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Right to Learn @BK 15 Juni 2022 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 9,549 Mal angesehen und es wurde von 150 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!
