Answer 1)
When you have duplicates in a table that has no primary key
defined, and you are using an older version of SQL Server, such as SQL Server
2000, you do not have an easy way to identify a single row. Therefore, you
cannot simply delete this row by specifying a WHERE clause in a DELETE statement.
You can, however, use the SET
ROWCOUNT 1 command, which
will restrict the subsequent DELETE statement to removing only one row.
For example:
DECLARE @table TABLE (dataVARCHAR(20))
INSERT INTO @table VALUES ('not duplicate row')
INSERT INTO @table VALUES ('duplicate row')
INSERT INTO @table VALUES ('duplicate row')
SET ROWCOUNT 1
DELETE FROM @table WHERE data = 'duplicate row'
SET ROWCOUNT 0
In the above example, only one row is deleted. Consequently,
there will be one remaining row with the content “duplicate row”. If you have
more than one duplicate of a particular row, you would simply adjust the ROWCOUNT accordingly. Note that
after the delete, you should reset the ROWCOUNT to 0 so that subsequent queries are
not affected.
| 
