"...it denies the client any knowledge that the insert failed"
But I am the client :-)
If it fails I don't care, because if a value exists it is by definition
'correct' and should not be replaced. If it works then it's just a
place holder until the real data arrives with the monthly Medispan data
update.

Thanks for the info, the query is just a place holder in a larger
insert/update stored procedure created by DBW, until I can finish
chapters 30 through 32 in your book.

The change I need to make is if the insert in the SP succeeds, I need to
do the second insert, and ignore PK (-803) errors.

-----Original Message-----
From: Helen Borrie [mailto:helebor@...]
Sent: Thursday, August 26, 2004 8:04 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] INSERT...SELECT...WHERE NOT EXISTS

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





Yahoo! Groups Links