Project Management | Talk about business development (Subhash Sharma, PMP, PSM1, Azure AZ 900, MCP): delete duplicate data from Table

Friday, March 12, 2010

delete duplicate data from Table

Note : You must have (Identity key) (EmpId) field in the table.

DELETE
FROM emp
WHERE empID NOT IN
(
SELECT MAX(empID)
FROM emp
GROUP BY empname)

2 comments:

anamikaMarch 26, 2010 at 8:49 AM
Hi,

This query is not affected.
First thing, if we declare EmpId as PRIMARY KEY then user can't enter duplicate records.

We can select duplicate record by this command:

SELECT NAME FROM EMPLOYEE
GROUP BY NAME
HAVING COUNT (*) > 1

But i m also confuse that how can we delete duplicate records? So please give me another suggestions.

Regards,
Lata Negi
ReplyDelete
Replies
Subhash Sharma PMP, PSM1, Azure AZ 900, MCPMarch 26, 2010 at 7:58 PM
You have to take identity field in the table....thats the limitation of that ..not primary key ...
that my mistake I written primary key..you must take one identity column in the table than you can use this query .
if you can delete data without any identity field than you can use cursors.......

DECLARE @Count int
DECLARE @User_Name varchar(50)
DECLARE @LastName varchar(50)

DECLARE dublicate_cursor CURSOR FAST_FORWARD FOR
SELECT User_Name, LastName, Count(*) - 1
FROM Users
GROUP BY User_Name, LastName
HAVING Count(*) > 1

OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO @User_Name, @LastName, @Count

WHILE @@FETCH_STATUS = 0
BEGIN

SET ROWCOUNT @Count
DELETE FROM Users WHERE User_Name = @User_Name AND LastName = @LastName
SET ROWCOUNT 0

FETCH NEXT FROM dublicate_cursor INTO @User_Name, @LastName, @Count
END

CLOSE dublicate_cursor
DEALLOCATE dublicate_cursor
ReplyDelete
Replies

Subscribe to: Post Comments (Atom)