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)
Watch video How to delete duplicate rows in a SQL table | Delete and Avoid duplicate entries in SQL | Ms SQL online without registration, duration hours minute second in high quality. This video was added by user Right to Learn @BK 15 June 2022, don't forget to share it with your friends and acquaintances, it has been viewed on our site 8,554 once and liked it 138 people.