INSERT INTO
CUSTOMER_TYPE (CUST_TYPE_COMPANY_ID, CUST_TYPE_DESC)
SELECT
1, 'General'
FROM
RDB$DATABASE
WHERE NOT EXISTS (
SELECT
1
FROM
CUSTOMER_TYPE
WHERE
CUST_TYPE_DESC = 'General');

You should still have a unique constraint on CUST_TYPE_DESC, as two
simultaneous inserts could still execute.
In my case this is useful as I have auto-ID triggers, and depending on
the constraint alone would cause generated IDs to be wasted.
This won't prevent someone from inserting [1, 'Generul'], so remember to
have some other identifier that should be unique (such as a federal tax
id).

-----Original Message-----
From: Myles Wakeham [mailto:myles@...]
Sent: Tuesday, February 22, 2005 1:42 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Why won't this work in Firebird?


INSERT INTO CUSTOMER_TYPE (CUST_TYPE_COMPANY_ID, CUST_TYPE_DESC) values
(1,'General') where not exists(SELECT CUST_TYPE_DESC from CUSTOMER_TYPE
where CUST_TYPE_DESC = 'General');

Any ideas?

Myles

===========================
Myles Wakeham
Director of Engineering
Tech Solutions Inc.
Scottsdale, Arizona USA
Phone (480) 451-7440
Web: www.techsol.org





Yahoo! Groups Links