Btukfyl

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

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 )

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!

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.

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.

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.

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

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

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.

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.

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)

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

)

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])

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

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

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