question

manjeet avatar image
manjeet asked

sql query needed me

how to remove duplicate row from a table in sql

sql-server-2005t-sqlrow-counts
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

use SET ROWCOUNT to limit the delete to 1 row (or however many duplicates need to be deleted)

set rowcount 1

delete from table where <condition is duplicate>

set rowcount 0

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

manjeet avatar image
manjeet answered

its not works give error near duplicate give me another alternate

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Manjeet: seriously?
1 Like 1 ·
hmmm "condition is duplicate" must not be valid SQL
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered

You have a table consisting of Forename & Surname:

DECLARE @TestTable TABLE (Forename VARCHAR(10), SurnameVARCHAR(10))
INSERT INTO @TestTable
SELECT 'Peter', 'Snow'
UNION ALL
SELECT 'Peter', 'Sissons'
UNION ALL
SELECT 'Peter', 'Snow' /* duplicate entry */
UNION ALL
SELECT 'John', 'Humphries'
UNION ALL
SELECT 'James', 'Naughtie'
UNION ALL
SELECT 'John', 'Humphries' /* duplicate entry */

How do you delete the duplicates?

One way is to use a cursor:

DECLARE @Forename VARCHAR(10)
DECLARE @Surname VARCHAR(10)
DECLARE NamCur CURSOR FOR /* Cursor with which to loop through all instances of forename / surname where we've got duplicates */
SELECT Forename, Surname
  FROM @TestTable
  GROUP BY Forename, Surname
  HAVING COUNT(*) > 1

OPEN NamCur -- open the cursor
FETCH NEXT FROM NamCur INTO @Forename, @Surname /* get the first row from the cursor */
SET ROWCOUNT 1 /* so we only act on one row at a time from here */
WHILE @@FETCH_STATUS <> -1 /* have we got data? /*
  BEGIN
    DELETE FROM @TestTable
      WHERE Forename = @Forename AND Surname = @Surname /* this is your duplicate condition */
    FETCH NEXT FROM NamCur INTO @Forename, @Surname /* get the next row */
  END
SET ROWCOUNT 0 /* reset the counter so we can act on an unlimited number of rows again */
CLOSE NamCur /* tidy up. */
DEALLOCATE NamCur

There are other ways of doing it, but this is relatively straightforward mentally.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.