CODEWITHSUNDEEP
sqloracle-databasedistinctsqlplus
Nausikaa
Nausikaa

Reputation: 123

Distinct not working?

I need to insert some field in a table. The table:

    CREATE TABLE RADAR(
    ctfoto  VARCHAR2(5),
    pkradar NUMBER(3,0),
    sradar  VARCHAR2(3),
    limitvelctera   NUMBER(3,0),
    limitvelradar NUMBER(3,0),  
    CONSTRAINT radar_pk PRIMARY KEY(ctfoto, pkradar, sradar)
);

The insert operation:

INSERT INTO RADAR(ctfoto, pkradar, sradar, limitvelctera, limitvelradar)
SELECT distinct carretera_foto, pto_km_radar, sentido_radar, limit_vel_ctera, limit_vel_radar FROM gotcha

The error:

ORA-00001: unique constraint (USER4704.RADAR_PK) violated

Please help.

Upvotes: 0

Views: 1132

Answers (2)

Adriano Carneiro
Adriano Carneiro

Reputation: 58665

You probably have more than one record in gotcha with the same values in fields carretera_foto, pto_km_radar, sentido_radar.

DISTINCT means the whole record will not be repeated.

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231881

DISTINCT applies to the entire set of columns you are selecting.

In all probability, you have rows in GOTCHA that have the same carretera_foto, pto_km_radar, and sentido_radar values but different values for one or both of the other two columns (limit_vel_ctera and limit_vel_radar). The DISTINCT in your SELECT cannot eliminate either of the rows because at least one value is different but the primary key constraint on the RADAR table rejects rows where the first three columns are identical.

Upvotes: 3

Related Questions