Rick DeBay wrote:

> I'm trying to create a fail-safe insert. I have this working elsewhere
> in the code, but this test statement keeps failing with the error:
>
> 'violation of PRIMARY or UNIQUE KEY constraint PK_DRUGQUANTITY on table
> DRUGQUANTITY'
>
> Statement:
> INSERT INTO DRUGQUANTITY (NDC,QTY)
> SELECT 123,456 FROM DRUGQUANTITY
> WHERE NOT EXISTS (SELECT NDC FROM DRUGQUANTITY WHERE NDC=123);
>
> Table and PK:
> CREATE TABLE DRUGQUANTITY
> (
> NDC D_NDC NOT NULL,
> QTY D_QUANTITY NOT NULL,
> CONSTRAINT PK_DRUGQUANTITY PRIMARY KEY (NDC)
> );
>
> Why does this fail? I can substitute other table name's after the FROM,
> but that fails too. Names for empty tables succeed, but no rows are
> inserted.

How many records will the select find? About as many as there are records
in the table? This will be the number of records inserted - all with
NDS=123 - hence the PK violation.


--
Aage J.