deleteing duplicate records ?

Hi,
This is my table structure

Name Age

Siva 24
Siva 24
Raghu 25

In this above table siva 24 row is inserted twice . how to delete duplicate record .

If you use SQL Server 2005 the following query will help you..

WITH MYTABLE as (select *, Row_Number() over (order by Name,age) RowId from names)
Delete from MyTable Where Rowid Not in(Select Min(rowId) from MyTable Group By Name,Age);

If you use SQL Server 2000 you should have minimum one unique column (id) to remove the duplicates.

|||

One way (that works with both SQL 2000 and SQL 2005) is to create a new table, add the non-duplicate data to the new table, then drop the old table and rename the new table to the same name as the old table.

Here is a demonstration:

Code Snippet

SET NOCOUNT ON

CREATE TABLE MyTable
( [Name] varchar(25),
[Age] int
)
GO

INSERT INTO MyTable VALUES ( 'Siva', 24 )
INSERT INTO MyTable VALUES ( 'Siva', 24 )
INSERT INTO MyTable VALUES ( 'Raghu', 25 )

SELECT *
FROM MyTable

CREATE TABLE MyNewTable
( [Name] varchar(25),
[Age] int
)
GO

INSERT INTO MyNewTable
SELECT
[Name],
[Age]
FROM MyTable
GROUP BY
[Name],
[Age]
SELECT *
FROM MyNewTable
DROP TABLE MyTable
GO

EXECUTE sp_rename 'MyNewTable', 'MyTable'

SELECT *
FROM MyTable

|||

In this case we can simply use,

Code Snippet

Select Distinct Name, Age Into MyNewTable From MyTable;

Truncate Table MyTable;

Insert Into MyTable Select * From MyNewTable;

Drop Table MyNewTable;