[sql] Delete duplicate records from a SQL table without a primary key

I have the below table with the below records in it

create table employee
(
 EmpId number,
 EmpName varchar2(10),
 EmpSSN varchar2(11)
);

insert into employee values(1, 'Jack', '555-55-5555');
insert into employee values (2, 'Joe', '555-56-5555');
insert into employee values (3, 'Fred', '555-57-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');
insert into employee values (1, 'Jack', '555-55-5555');
insert into employee values (4, 'Mike', '555-58-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6 ,'Lisa', '555-70-5555');
insert into employee values (5, 'Cathy', '555-59-5555');
insert into employee values (6, 'Lisa', '555-70-5555');

I dont have any primary key in this table .But i have the above records in my table already. I want to remove the duplicate records which has the same value in EmpId and EmpSSN fields.

Ex : Emp id 5

Can any one help me to frame a query to delete those duplicate records

Thanks in advance

This question is related to sql sql-server-2005 tsql duplicate-removal

The answer is


Having a database table without Primary Key is really and will say extremely BAD PRACTICE...so after you add one (ALTER TABLE)

Run this until you don't see any more duplicated records (that is the purpose of HAVING COUNT)

DELETE FROM [TABLE_NAME] WHERE [Id] IN 
(
    SELECT MAX([Id])
    FROM [TABLE_NAME]
    GROUP BY [TARGET_COLUMN]
    HAVING COUNT(*) > 1
)


SELECT MAX([Id]),[TABLE_NAME], COUNT(*) AS dupeCount
FROM [TABLE_NAME]
GROUP BY [TABLE_NAME]
HAVING COUNT(*) > 1

MAX([Id]) will cause to delete latest records (ones added after first created) in case you want the opposite meaning that in case of requiring deleting first records and leave the last record inserted please use MIN([Id])


I'm not an SQL expert so bear with me. I'm sure you'll get a better answer soon enough. Here's how you can find the duplicate records.

select t1.empid, t1.empssn, count(*)
from employee as t1 
inner join employee as t2 on (t1.empid=t2.empid and t1.empssn = t2.empssn)
group by t1.empid, t1.empssn
having count(*) > 1

Deleting them will be more tricky because there is nothing in the data that you could use in a delete statement to differentiate the duplicates. I suspect the answer will involve row_number() or adding an identity column.


ITS easy use below query

WITH Dups AS
(
  SELECT col1,col2,col3,
ROW_NUMBER() OVER(PARTITION BY col1,col2,col3 ORDER BY (SELECT 0)) AS rn
 FROM mytable
)
DELETE FROM Dups WHERE rn > 1

DELETE FROM 'test' 
USING 'test' , 'test' as vtable
WHERE test.id>vtable.id and test.common_column=vtable.common_column  

Using this we can remove duplicate records


create unique clustered index Employee_idx
on Employee ( EmpId,EmpSSN )
with ignore_dup_key

You can drop the index if you don't need it.


select distinct * into newtablename from oldtablename

Now, the newtablename will have no duplicate records.

Simply change the table name(newtablename) by pressing F2 in object explorer in sql server.


no ID, no rowcount() or no temp table needed....

WHILE 
  (
     SELECT  COUNT(*) 
     FROM TBLEMP  
     WHERE EMPNO 
            IN (SELECT empno  from tblemp group by empno having count(empno)>1)) > 1 


DELETE top(1)  
FROM TBLEMP 
WHERE EMPNO IN (SELECT empno  from tblemp group by empno having count(empno)>1)

If you don't want to create a new primary key you can use the TOP command in SQL Server:

declare @ID int
while EXISTS(select count(*) from Employee group by EmpId having count(*)> 1)
begin
    select top 1 @ID = EmpId
    from Employee 
    group by EmpId
    having count(*) > 1

    DELETE TOP(1) FROM Employee WHERE EmpId = @ID
end

You could create a temporary table #tempemployee containing a select distinct of your employee table. Then delete from employee. Then insert into employee select from #tempemployee.

Like Josh said - even if you know the duplicates, deleting them will be impossile since you cannot actually refer to a specific record if it is an exact duplicate of another record.


there are two columns in the a table ID and name where names are repeating with different IDs so for that you may use this query: . .

DELETE FROM dbo.tbl1
WHERE id NOT IN (
     Select MIN(Id) AS namecount FROM tbl1
     GROUP BY Name
)

delete sub from (select ROW_NUMBER() OVer(Partition by empid order by empid)cnt from employee)sub where sub.cnt>1


With duplicates

As
(Select *, ROW_NUMBER() Over (PARTITION by EmpID,EmpSSN Order by EmpID,EmpSSN) as Duplicate From Employee)

delete From duplicates

Where Duplicate > 1 ;

This will update Table and remove all duplicates from the Table!


Code

DELETE DUP 
FROM 
( 
    SELECT ROW_NUMBER() OVER (PARTITION BY Clientid ORDER BY Clientid ) AS Val 
    FROM ClientMaster 
) DUP 
WHERE DUP.Val > 1

Explanation

Use an inner query to construct a view over the table which includes a field based on Row_Number(), partitioned by those columns you wish to be unique.

Delete from the results of this inner query, selecting anything which does not have a row number of 1; i.e. the duplicates; not the original.

The order by clause of the row_number window function is needed for a valid syntax; you can put any column name here. If you wish to change which of the results is treated as a duplicate (e.g. keep the earliest or most recent, etc), then the column(s) used here do matter; i.e. you want to specify the order such that the record you wish to keep will come first in the result.


Use the row number to differentiate between duplicate records. Keep the first row number for an EmpID/EmpSSN and delete the rest:

    DELETE FROM Employee a
     WHERE ROW_NUMBER() <> ( SELECT MIN( ROW_NUMBER() )
                               FROM Employee b
                              WHERE a.EmpID  = b.EmpID
                                AND a.EmpSSN = b.EmpSSN )

It is very simple. I tried in SQL Server 2008

DELETE SUB FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt
 FROM Employee) SUB
WHERE SUB.cnt > 1

delete from employee where rowid in (select rowid from (select rowid, name_count from (select rowid, count(emp_name) as name_count from employee group by emp_id, emp_name) where name_count>1))


ALTER IGNORE TABLE test
           ADD UNIQUE INDEX 'test' ('b'); 

@ here 'b' is column name to uniqueness, @ here 'test' is index name.


select t1.* from employee t1, employee t2 where t1.empid=t2.empid and t1.empname = t2.empname and t1.salary = t2.salary
group by t1.empid, t1.empname,t1.salary having count(*) > 1

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server-2005

Add a row number to result set of a SQL query SQL Server : Transpose rows to columns Select info from table where row has max date How to query for Xml values and attributes from table in SQL Server? How to restore SQL Server 2014 backup in SQL Server 2008 SQL Server 2005 Using CHARINDEX() To split a string Is it necessary to use # for creating temp tables in SQL server? SQL Query to find the last day of the month JDBC connection to MSSQL server in windows authentication mode How to convert the system date format to dd/mm/yy in SQL Server 2008 R2?

Examples related to tsql

Passing multiple values for same variable in stored procedure Count the Number of Tables in a SQL Server Database Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Stored procedure with default parameters Format number as percent in MS SQL Server EXEC sp_executesql with multiple parameters SQL Server after update trigger How to compare datetime with only date in SQL Server Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot Printing integer variable and string on same line in SQL

Examples related to duplicate-removal

C# LINQ find duplicates in List Eliminating duplicate values based on only one column of the table Delete duplicate records from a SQL table without a primary key