Submitted by st on
Comment supprimer les lignes dont les valeurs d'une ou plusieurs colonnes sont dupliquées ? Voici une exemple en Transact SQL.
CREATE TABLE #t ( product_name nvarchar(20), vendor_name nvarchar(20) ) GO INSERT INTO #t (product_name, vendor_name) SELECT 'SQL Server', 'Microsoft' UNION ALL SELECT 'Oracle', 'Oracle' UNION ALL SELECT 'DB2', 'IBM' UNION ALL SELECT 'Oracle', 'Oracle' UNION ALL SELECT 'Oracle', 'Oracle' UNION ALL SELECT 'DB2', 'IBM' UNION ALL SELECT 'DB2', 'IBM' UNION ALL SELECT 'DB2', 'IBM' GO 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
Résultats
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