jump to navigation

Delete duplicate rows in a table in SQL Server June 2, 2008

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
trackback

Everybody knows that when we create a database and design a table we should always set a primary key.

There are some tables that have no primary key. So duplicate records are entered. the problem becomes even greater when the records are identical, meaning that all the fields are the same for those two records.

We will try to delete one of these 2 identical rows. Firstly we muct create our sample table and then populate it with duplicate data.

CREATE TABLE [dbo].[theauthors](
 [au_id] [dbo].[id] NOT NULL,
 [lastname] [varchar](40) NOT NULL,
 [firstname] [varchar](20) NOT NULL,

)

GO

Now we insert the data (2 duplicate records) in the table

INSERT INTO [dbo].[theauthors] VALUES(1, ‘Charles’,’Dickens’)
INSERT INTO [dbo].[theauthors] VALUES(2, ‘Jane’,’Austen’)
INSERT INTO [dbo].[theauthors] VALUES(3, ‘Karen’,’White’)
INSERT INTO [dbo].[theauthors] VALUES(1, ‘Charles’,’Dickens’)
INSERT INTO [dbo].[theauthors] VALUES(4, ‘James’,’Joyce’) 

GO

if we select the data from our table we have the following results

select * from theauthors

 au_id       lastname                                 firstname
———– —————————————-
1           Charles                                  Dickens
2           Jane                                     Austen
3           Karen                                    White
1           Charles                                  Dickens
4           James                                    Joyce

 

In order to delete the duplicate record we must use a simple DELETE statement with a TOP command.
DELETE TOP(1) FROM [dbo].[theauthors] WHERE au_id = 1

Comments»

1. satya prakash - June 10, 2008

THANKS FOR UR HELP


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: