Document information

Document ID:	760
Subject:	Deleting duplicate rows with no primary key
Creation date:	7/29/09 12:15 PM
Last modified on:	7/29/09 12:15 PM

Details

Consider the following scenario. You have a table with following design

Field Name          Data Type    Primary key   
-----------         ----------   ------------
ID                  INTEGER         NO
FNAME               VARCHAR(30)     NO

There is no primary on the table and there are duplicates rows in the table. The data looks like:

ID        NAME
-------   ---------
1         Joe
2         Mary
1         Joe

Note that the row where NAME is Joe appears twice in the table and you want to delete one of them without affecting the other.

Following queries will delete only one row from the table.

set rowcount 1 -- Cause SQL Server to stop processing after
-- one row is modified
go
delete from dbo.TestTable
where FNAME = 'Joe'
go
set rowcount 0 -- Changes the row count back to default

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users? Please add it below. Your name and tip will appear at the end of the document text.

Your name:
Your email:	Hide my email address
Verification code:	Enter the verification code you see above more submitting your tip
Tip:	Please limit tips to 1000 characters