General discussion

Locked

Oracle 8x SQL or PL/SQL Script for dedup

By DynaNIN ·
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).

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Oracle 8x SQL or PL/SQL Script for dedup

by DynaNIN In reply to Oracle 8x SQL or PL/SQL S ...

-- 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 ...

Collapse -

Oracle 8x SQL or PL/SQL Script for dedup

by sparent In reply to Oracle 8x SQL or PL/SQL S ...

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.

Collapse -

Oracle 8x SQL or PL/SQL Script for dedup

by DynaNIN In reply to Oracle 8x SQL or PL/SQL S ...

The question was auto-closed by TechRepublic

Collapse -

Oracle 8x SQL or PL/SQL Script for dedup

by GIJoe In reply to Oracle 8x SQL or PL/SQL S ...

Is this still a problem?

Collapse -

Oracle 8x SQL or PL/SQL Script for dedup

by DynaNIN In reply to Oracle 8x SQL or PL/SQL S ...

The question was auto-closed by TechRepublic

Collapse -

Oracle 8x SQL or PL/SQL Script for dedup

by DynaNIN In reply to Oracle 8x SQL or PL/SQL S ...

This question was auto closed due to inactivity

Back to IT Employment Forum
6 total posts (Page 1 of 1)  

General Discussion Forums