Add new comment

SQL: delete duplicated rows

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