Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!ucsd!ucbvax!ucdavis!csusac!unify!Unify.com!jeff
From: jeff@Unify.com (Jeff Mischkinsky)
Newsgroups: comp.databases
Subject: Re: Ingres Question: Duplicate Rows
Message-ID: <1990Oct4.193912@Unify.com>
Date: 5 Oct 90 02:39:12 GMT
References: <1990Aug8.230913.2897@agate.berkeley.edu> <26867@pasteur.Berkeley.EDU> <12314@blia.BLI.COM>
Sender: news@Unify.Com (news admin)
Reply-To: jeff@Unify.com (Jeff Mischkinsky)
Organization: Unify Corporation, Sacramento, CA, USA
Lines: 35

In article <12314@blia.BLI.COM>, miket@blia.BLI.COM (Mike Tossy) writes:
> In article <26867@pasteur.Berkeley.EDU>, mao@eden (Mike Olson) writes:
>  
> > ...   the fact that most commercial systems
> > (well, okay, all commercial systems) permit dups is testimony to the
> > difficulty of detecting and removing them, and not to their desirability.

Actually I think the reason we don't remove them is beacause our customers
demand them.  There is nothing particularly hard about detecting and
removing duplicates, although it may be costly.  If you don't want
dups then use DISTINCT, UNIQUE, or whatever keyword your favorite
query language supports in you queries, and/or specify NOT NULL, UNIQUE
in your schema definitions.
> 
> The ANSI Standard specificly allows duplicates. This is probably unfortunate.
> (Luckily :->  they aren't very useful since that the standard provides no 
> way to interactively update only one of them.)  This is one of those areas

Although you can update them individually using a cursor.

...deleted...

> Absolutely correct!  Duplicates in base tables are a poor idea even when
> permitted.  However unless you really pay attention, it is fairly easy to
> create temporary derived tables (say for decision support) which do have
> duplicates because you didn't include all the base tables' primary keys.
> The SQL semantics are much more likely to give you the desiered answer in
> this case.
If you really care use DISTINCT to get rid of the dups.

--
Jeff Mischkinsky		internet: jeff@unify.com
Unify Corporation		          ...!{pyramid,csusac}!unify!jeff
3870 Rosin Court		voice: (916) 920-9092 fax: (916) 921-5340
Sacramento, CA 95834		ICBMS: 38 38 40 N / 120 28 10 W