SQL: delete duplicated rows

| category: Programming | author: st
Tags:

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

blog comments powered by Disqus