Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!crdgw1!gecrdvm1!syspmzt
From: SYSPMZT@gecrdvm1.crd.ge.com
Newsgroups: comp.databases
Subject: Re: SQL Duplicate Row Deletion ???
Message-ID: <91092.153113SYSPMZT@GECRDVM1.BITNET>
Date: 2 Apr 91 19:31:13 GMT
References: <91091.141528SYSPMZT@GECRDVM1.BITNET>
 <1991Apr1.163615.56@cim-vax.honeywell.com>
Organization: General Electric Corporate Research & Development
Lines: 33

In article <1991Apr1.163615.56@cim-vax.honeywell.com>,
tdoyle@cim-vax.honeywell.com says:
>
>In article <91091.141528SYSPMZT@GECRDVM1.BITNET>, SYSPMZT@gecrdvm1.crd.ge.com
>writes:
>> I've made a nice mistake loading data twice over several days into a table,
>and
>> would like to delete just one of the duplicate rows.                        s
>> Anyone have a nifty solution to this problem?  The database manager is DB2,
>>
>> Phil Zampino
>

>Select identified dupicates into a temporary table (SELECT UNIQUE INTO TEMP).
>Then delete from the original where the record is the same as in TEMP.
>This will rid ALL instances of the duplicates (including the original).
>Then add records from TEMP into ORIG to restore one copy.
>
>Bipin Prasad bprasad@honcim1.honeywell.com

Thanks; I got a lot of personal replies with some clever suggesstions to this
problem.  A similar solution is:

Create temp_table like original_table
Select distinct * from original_table into temp_table
Delete from original_table
Select * from temp_table into original_table.

I am now the happy owner of a uniquely rowed table, and plan to put a unique
index on the table to protect it from my own stupid actions.

Thanks to all replying,
      Phil Zampino