Delete only record from Duplicate records

Hi Folks
I need to delete only 1 row out of same duplicate rows from my table. for example same supplier exist 10 times and another 5 times. I want ot delete 1 records from each of supplier like records left after deletion e.g 9 for first supplier and 4 for second supplier..

regards

it has been discussed many times.
Please search for it.
also read this:
http://www.sql-server-performance.com/dv_delete_duplicates.asp

Hi Sonny,

Try this!! Of course u hv to mk few changes before it works on ur data structure.

CREATE PROCEDURE proc1
AS
BEGIN
SET NOCOUNT ON

DECLARE @tempTable TABLE
(
col1 INT
)

INSERT INTO @tempTable
SELECT COL1 FROM TABLE1 GROUP BY COL1 HAVING COUNT(COL1)>1

DECLARE @ID INT
DECLARE @tempWorkTable TABLE
(
col1 INT
)

SELECT TOP 1 @ID=COL1 FROM @tempTable ORDER BY 1

WHILE EXISTS (SELECT * FROM @tempTable)
BEGIN
INSERT INTO @tempWorkTable
SELECT COL1 FROM TABLE1 WHERE COL1=@ID

DELETE TABLE1 WHERE COL1=@ID

DECLARE @CURROWCOUNT INT
SELECT @CURROWCOUNT=COUNT(*) FROM @tempWorkTable

WHILE (@CURROWCOUNT>1)
BEGIN
INSERT INTO TABLE1
SELECT TOP 1 COL1 FROM @tempWorkTable ORDER BY 1
SET @CURROWCOUNT=@CURROWCOUNT-1
END

DELETE @tempWorkTable
DELETE @tempTable WHERE COL1=@ID
SELECT TOP 1 @ID=COL1 FROM @tempTable ORDER BY 1
END
END
GO

Thanks

saransh

DELETE FROM tbl
WHERE EXISTS
(SELECT * FROM tbl t1
WHERE t1.key = tbl.key AND t1.PK < tbl.PK)

This leaves only the row with the lowest PK for the same key value. If the table does not already have a Primary Key, then add an IDENTITY column as the PK, and then run the query.

Slightly unusual request. If you have true duplicate entries, you would want to keep just one entry.

Hi Guys... once again thanks a lot.. It is always feel good when people help other like this..
yes ranjit... I did find more articles on it but the one find on this site is best suit my requirement.
saransh,Adriaan...thanks for script..
Adriaan .. yes it is unusual.. cos I want to flagged only on record out of duplicate records after shift them into temp table and delete only one record out of duplicate records in original table and shift back flagged records to the original table. so, in this way I will have original number of records in staging table as well as the single record for each combination (flagged one).. I hope you get it..

regards

if you want to remove duplicate records with ntext,text and image datatype .please do the below link stap.
http://www.codegroups.com/blog/inde...rom-table-with-text-ntext-or-image-data-type/
I hope this is help !
Regards,
Shaileshk