Re: inserting distinct values from another table

From: <fitzjarrell_at_cox.net>
Date: 14 May 2005 13:28:09 -0700
Message-ID: <1116102489.588229.20500@g49g2000cwa.googlegroups.com>

m.mo..._at_gmail.com wrote:
> I'm not sure why this doesn't work:
>
> insert into courses (CCode, Title, SCode)
> select distinct CCode, Title, SCode
> from TMPT05
>
> I still get the ERROR at line 1:
> ORA-00001: unique constraint (SYSTEM.SYS_C003020) violated

How is your constraint defined? The query you posted returns distinct COMBINATIONS of the three column values; only the combination of all THREE is distinct. If your primary key or unique constraint consists of one or two of those three values you WILL get duplicates which violate your constraint.

Brush up on your set theory, as DISTINCT is a set operator.

David Fitzjarrell Received on Sat May 14 2005 - 15:28:09 CDT