SQL: delete duplicated rows — Mechanics of software

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

Mechanics of software Programming is thinking, not typing
Home \| Categories \| Tags \| Search	\| eng \| рус