Submitted by st on
How to delete the rows having the duplicated values of one or more columns and considered as duplicates? The example in Transact SQL is below.
SET NOCOUNT ON; CREATE TABLE #t ( product_name nvarchar(20), vendor_name nvarchar(20) ) GO INSERT INTO #t (product_name, vendor_name) VALUES ('SQL Server', 'Microsoft'), ('Oracle', 'Oracle'), ('DB2', 'IBM'), ('Oracle', 'Oracle'), ('Oracle', 'Oracle'), ('DB2', 'IBM'), ('DB2', 'IBM'), ('DB2', 'IBM'); SELECT * FROM #t; DECLARE @col1 nvarchar(255), @col2 nvarchar(255), @dup_count int; WHILE 1 = 1 BEGIN SELECT TOP 1 @col1 = product_name, @col2 = vendor_name, @dup_count = COUNT(1) - 1 FROM #t GROUP BY product_name, vendor_name HAVING COUNT(1) > 1 IF @@ROWCOUNT = 0 BREAK; DELETE TOP (@dup_count) FROM #t WHERE @col1 = product_name AND @col2 = vendor_name; END SELECT * FROM #t; GO DROP TABLE #t
Results
product_name vendor_name -------------------- -------------------- SQL Server Microsoft Oracle Oracle DB2 IBM Oracle Oracle Oracle Oracle DB2 IBM DB2 IBM DB2 IBM product_name vendor_name -------------------- -------------------- SQL Server Microsoft Oracle Oracle DB2 IBM