Discussion Locked

Oracle 8x SQL or PL/SQL Script for dedup

0 helpful
6 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
For CRM app ... I'm looking for a piece of SQL code that will enable me to eliminate duplicate records in a database using a key (like CUSTOMER_ID) with the added twist of a sort criteria (like keeping the most recent CONTACT_DATE records for each customer).

6 replies

by dynanin

-- Last suggesting calls for posting a new -- table as follows:
INSERT INTO NEW ...
SELECT DISTINCT CUSTOMER_ID, MAX CONTACT_DT, MAX(....)
FROM OLD
GROUP BY CUSTOMER_ID
ORDER BY CUSTOMER_ID;
-- This works for now ...

by sparent

If you simply want to remove the duplicate rows, you can simply delete the duplicates right out of the table.

DELETE FROM old o1
WHERE EXISTS
(SELECT 1
FROM old o2
WHERE o2.customer_id = o1.customer_id
AND o2.contact_date > o1.contact_date)

You can first try the above as a SELECT statement first, until you're happy that you are in fact removing the correct rows.

This discussion is closed to new replies.