how to remove duplicate row from a table in sql
its not works give error near duplicate give me another alternate
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.
No one has followed this question yet.