Re: SQL Duplicate Row Deletion ???

Path: emory!swrinde!cs.utexas.edu!bcm!convex!egsner!clifton From: clifton@egsner.cirr.com (Clifton Bean) Newsgroups: comp.databases 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> Organization: Central Iowa (Model) Railroad, Dallas, Tx. 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)