At 05:13 PM 26/08/2004 -0400, you 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.

You'd need to use aliases for the two re-entrant subqueries:

INSERT INTO DRUGQUANTITY (NDC,QTY)
SELECT 123,456 FROM DRUGQUANTITY dq1
WHERE NOT EXISTS (SELECT dq2.NDC FROM DRUGQUANTITY dq2
WHERE dq2.NDC=123);

But this isn't a "fail-safe insert". Sure, it allows the insert to fail
without returning an exception, but it denies the client any knowledge that
the insert failed. I'd call that a "fail-unsafe insert".

And there will still be an exception if the existence test succeeds and
another transaction has a conflicting insert pending on the same row.

"Fail-safe" means catching exceptional conditions and giving the user the
opportunity to correct them.

./heLen