Re: Help with duplicate records in SQL

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);


Dale Cooper, DBA			"...and they ask me why I drink"