Senator Guerra Souty original series calendar,replica hublot blue steel peach pointer collocation of rolex replica Rome digital scale, track type minute replica watches scale shows that the classical model is swiss replica watches incomparable, wearing elegant dress highlights.


YOU ARE HERE: HOME Questions In a table there are so many duplicated records with your name having 4 records there is no primary

Removing Duplicate Rows in SQL Server for Tables with no primary key

View(s): 30413

In a table there are so many duplicated records, with your name having 4 records, there is no primary key, then how can you delete two records having with your name?

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')

DELETE FROM @table WHERE data 'duplicate row'

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.

  Asked in:  iGATE   Expertise Level:  Experienced
  Last updated on Monday, 10 March 2014
4/5 stars (21 vote(s))

Register Login Ask Us Write to Us Help