WinSQL » Knowledge base

Document information

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

Details

Consider the following scenario. You have a table with the 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 the row where NAME Joe appears twice in the table. If you want to delete one of them without affecting the other follow the queries below:

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?

Your Name

Email

Hide my email address

Captcha

Your Tip

Submit Tip