|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|
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:
Note that the row where
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
delete from dbo.TestTable
where FNAME = 'Joe'
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.