Re: Identify duplicate keys

From: Yves Noel <noel_at_citi.citilille.fr>
Date: 1 Apr 1993 10:12:28 GMT
Message-ID: <1pef6cINNd39_at_netserver.univ-lille1.fr>

I've received several mails about my transaction concerning an article in the 'Bulletin Technique Oracle-France' number 5, which traited the elimination of duplicate keys in a table. I think the best is to translate this paper here. I don't know who is the exactly author but he's one of these : Alexandre AVRANE, Bernard BORBELY, Sylvie DALBERA, Tuan DINH, Philippe LAWSON, Konrad MAKOMASKI, Francois ORSINI, Patrick ROLLAND. By advance excuse my English translate which won't always very good !

---

Bulletin Technique ORACLE-FRANCE, number 5, autumn 1989

---

The script accept four input parameters :

1 - the table name
2 - name(s) of column(s) (separated by commas)
3 - the level of duplicates keys to treat
4 - the occurence level (possible values are '=', '>', '>=', '^=', ...)

The second parameter is used to specify the desired duplicate notion, what's the question ? Rows with the same value on a distinct column ? Rows with same values on ALL columns of a table ? In this last case, it's necessary to give the name of all columns in this second parameter. All mixings are possible.

Third and fourth parameters are used to specify the level of duplicate : if we wish to delete tripled keys without eliminate quadrupled, we'll used 3 and '='. To delete all duplicate keys, without level notion, we'll used 2 and '>=' or 1 and '>'. Finally, to delete duplicated, tripled, quadrupled keys on keeping quintupled, we'll used 5 and '<'.

Last remark : deleting shall keep rows with the smallest ROWID, which correspond  generaly to oldest rows in the table.

Here is the script :

#

undef table
undef key
undef nbr_occur
undef sign

accept table            char   prompt "Table name : ........................"
accept key              char   prompt "Keyed column(s) : ..................."
accept nbr_occur        number prompt "Number of occurences : .............."
accept sign             char   prompt "Level of occurences ? '=' or '>' : .."

#

rem We lock the table
set termout off
select '1' from &&table for update of &&key; set termout on
#

delete from &&table where

        (&&key, rowid) in
        (
                (
                 select &&key, rowid from &&table where
                 (&&key) in
                        (
                         select &&key from &&table
                                group by &&key having count(*) &&sign &&nbr_occur
                        )
                )
                minus
                (
                 select &&key, min(rowid) from &&table
                        group by &&key having count(*) &&sign &&nbr_occur
                )
        )

/ Received on Thu Apr 01 1993 - 12:12:28 CEST