Re: SQL Too difficult for me do you know how?

From: Em Pradhan <empradhan_at_dplus.net>
Date: Thu, 04 Mar 1999 21:53:18 -0500
Message-ID: <36DF471E.AF9F71A3@dplus.net>

Hi

    I think Sybrand Bakker's sql statement will delete one of the duplicate. But it also delete a row if there exists only one row which is unique for columns clientID and AddressID
as in your example records if you apply Sybrand Bakker's sql statement you will be left with only

RecordID ClientID AddressID

     1                    688            123

I think you definetly didn't want that to happen. recordID 3 and 4 are same client but different locations so I am guessing that you want to keep that. I am pretty sure that Sybrand's statement will delete all the records where you have only one record for a client.

there may be different easy solutions but at moment what I can think of is as follows:

/*****************************/

create table temp_table as
select ClientID, AddressID,count(RecordID) num_row from the_table group by ClientID, AddressID
having count(RecordID) > 1;
Begin

    declare

        cursor c is select * from temp_table; Begin

        for c_rec in c loop
           delete from the_table
            where ClientID = c_rec.ClientID
            and     AddressID = c_rec.AddressID
            and     rownum < c_rec.num_row;
            commit;
        end loop;

end;
end;
/
Drop table temp_table;

/******************************/

As I say there may be easier solution that this so I will wait for some more ideas..

HTH.. Pradhan

Jobi wrote:

> Hi,
>
> I have a Table with somewhat duplicate record and I would like to delete
> the duplicates Here is an Exemple of the table:
>
> RecordID ClientID AddressID
>
> 1 688 123
> 2 688 123
> 3 783 142
> 4 783 143
>
> Note: RecordID is created on Insert to provide a Unique Number to Index On.
> It is NOT ROWID
>
> Now What I would like to do is get rid of All but ONE of the Records that
> Have the Same ClientID AND AddressID....
>
> Thanks for your help....
>
> Eric
Received on Thu Mar 04 1999 - 20:53:18 CST