Saturday, June 28, 2008

How to delete duplicate rows from a SQL table, which has no Primary Key?

Sql provide RowCount to Delete a Duplicate rows from a table which has no Primary key:
1. In Sql2000 one can use rowcount while deleting duplicate rows
2. In Sql2005 one can use rowcount and top while deleting duplicate rows
3. But according to MicroSoft:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax.


The following statement(s) have better way to resolve above:-
CREATE TABLE dbo.checkDuplicate
(
[ID] [int] ,
[FirstName] [varchar](25),
[LastName] [varchar](25)
) ON [PRIMARY]

INSERT INTO dbo.checkDuplicate VALUES(1, 'Gaurav','Arora')
INSERT INTO dbo.checkDuplicate VALUES(2, 'Shuby','Arora')
INSERT INTO dbo.checkDuplicate VALUES(3, 'Amit','Gupta')
INSERT INTO dbo.checkDuplicate VALUES(1, 'Gaurav','Arora')
INSERT INTO dbo.checkDuplicate VALUES(5, 'Neelima','Malhotra')
INSERT INTO dbo.checkDuplicate VALUES(4, 'Shweta','Arora')
INSERT INTO dbo.checkDuplicate VALUES(4, 'Shweta','Arora')
INSERT INTO dbo.checkDuplicate VALUES(2, 'Meghna','Arora')

SELECT * FROM dbo.checkDuplicate
SELECT * FROM dbo.checkDuplicate WHERE ID = 1 AND FirstName = 'Gaurav' AND LastName = 'Arora'

--To Delete duplicates, one should use rowcont as follows[for SQL2000/2005]:
SELECT * FROM dbo.checkDuplicate

SET ROWCOUNT 1
DELETE FROM dbo.checkDuplicate WHERE ID = 1
SET ROWCOUNT 0

SELECT * FROM dbo.checkDuplicate

--In SQL2005, one should use Top as follows:
SELECT * FROM dbo.checkDuplicate
DELETE TOP(1) FROM dbo.checkDuplicate WHERE ID = 4
SELECT * FROM dbo.checkDuplicate

--Following is an update method to delete row(s)-For SQL2005:

--If you use the following command this will get a count of how many rows
--there are and delete this minus one record.

SELECT * FROM dbo.checkDuplicate

DELETE TOP (SELECT COUNT(*) -1 FROM dbo.checkDuplicate WHERE ID = 1)
FROM dbo.Emptest
WHERE ID = 1

SELECT * FROM dbo.checkDuplicate


Copy and Paste above snippet and try.

No comments:

Post a Comment