Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!cs.utexas.edu!bcm!convex!egsner!clifton
From: clifton@egsner.cirr.com (Clifton Bean)
Newsgroups: comp.databases
Subject: Re: SQL Duplicate Row Deletion ???
Message-ID: <1991Apr7.054945.8610@egsner.cirr.com>
Date: 7 Apr 91 05:49:45 GMT
References: <91091.141528SYSPMZT@GECRDVM1.BITNET> <1991Apr1.163615.56@cim-vax.honeywell.com> <670651675.103469@paladin.owego.ny.us>
Distribution: na
Organization: Central Iowa (Model) Railroad, Dallas, Tx.
Lines: 24

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.
 
Anyone have a nifty solution to this problem?  The database manager is DB2, but
I'd think that any SQL based language would have the same problem.

------------------------------------------------------------------------------

I use Informix.  Their tables all contain a "hidden" index on the row_id of
the records within the table.  In Informix, I would code the following:

                 Select rowid, * from table
                  where ... = ...  (you supply the fieldname and contents)

You would then use one of the row_ids in your delete from table statement.

Check your documentation's index for a reference to this type of "automatic"
index - I would think this type of index is a logical part of any sql package.
-- 
*******************************************************************************
Clifton M. Bean                          USENET: clifton@lodestar.lonestar.org
Page & Addison, P.C., Dallas, TX           UUCP: ...!egsner!lodestar!clifton
Work Phone: (214) 960-0933 (9-6 CDT)     Home Phone: (214) 307-2242 (pm CDT)