Re: Help with duplicate records in SQL

In article <50845_at_seismo.CSS.GOV} cooper_at_beno.CSS.GOV (Dale Cooper) writes:
}In article <ALAW.92Jun8133451_at_ap1seq.oracle.com} alaw_at_us.oracle.com writes:
}}In article <1992Jun4.212719.3024_at_ncube.com} sbh_at_ncube.com (Shawn Honess) writes:
}-}I have a table with some duplicate records. I would like
}-}to delete all duplications, leaving one of each record. I
}-}use essenitally the following search to find the duplicates:
 

}-} SELECT part, name, vendor, status
}-} FROM bigtable
}-} GROUP BY part, name, vendor, status
}-} HAVING COUNT(*) } 1 <=== this is the key
}-} ORDER BY part, name, vendor, status
}-} ;
 

}-Try
 

}- DELETE FROM bigtable
}- WHERE rowid = (
}- SELECT max(rowid)
}- FROM bigtable
}- GROUP BY part, name, vendor, status
}- HAVING COUNT(*) } 1
}- )
}- ;
 

}BZZZZZT!
 
}If there are more than 1 set of duplicates found, this query will return:
 

}SELECT max(rowid)
}*
}ERROR at line 3:
}ORA-01427: single-row subquery returns more than one row
 

}instead, try:
 

} DELETE FROM bigtable
} WHERE rowid in (
} SELECT max(rowid)
} FROM bigtable
} GROUP BY part, name, vendor, status
} HAVING COUNT(*) } 1);
}

This will work for dupes, but if your info has triplicates or worse you need to run it multiple times.

Change the 'in' to 'not in'.

-- 
-------------------------------------------------------------------------------
I am not an official Oracle spokesman.  I speak for myself and no one else.