How to Delete SQL Server Database Duplicate Rows from a Table Using a Specified Column List and No Temp Tables
SELECT COUNT(*),
Column1,
Column2,
.
.
Columnn
FROM TestTable
GROUP BY
Column1,
Column2,
.
.
Columnn
HAVING COUNT(*) > 1
--find duplicate emails in a 'users' table:
SELECT id, email, count(email) FROM users GROUP BY email HAVING count(email) > 1
--Another approach is to list all the duplicate entries. We assume the field 'id' is the primary key of the table 'users'
SELECT DISTINCT t1.id, t1.email FROM users t1, users t2 WHERE t1.email = t2.email AND t1.id <> t2.id
/*
How to find duplicates in multiple columns
I have a table with columns b and c that links two other tables b and c, and I want to find all rows that have duplicates in either b or c.
*/
create table a_b_c(
a int not null primary key auto_increment,
b int,
c int
);
insert into a_b_c(b,c) values (1, 1);
insert into a_b_c(b,c) values (1, 2);
insert into a_b_c(b,c) values (1, 3);
insert into a_b_c(b,c) values (2, 1);
insert into a_b_c(b,c) values (2, 2);
insert into a_b_c(b,c) values (2, 3);
insert into a_b_c(b,c) values (3, 1);
insert into a_b_c(b,c) values (3, 2);
insert into a_b_c(b,c) values (3, 3);
select b, c, count(*)
from a_b_c
group by b, c
having
count(distinct b) > 1
or count(distinct c) > 1;