On Wed, Oct 19, 2011 at 8:40 PM, Ed Grochowski <gastrocus@...> wrote:

> Using SQL only (no stored procedures), how do I construct a statement to
> insert values into a database table, if and only if, a row containing a
> field with a specific value does not already exist?
>

A variety of people, including some very knowledgeable long time Firebird
experts, have suggested a variety of solutions using new and exotic SQL
commands, some of which might work, on a single user system.
Unfortunately, none of them will work reliably on an MVCC database with
concurrent inserts or updates to the table. To use the example discussed,
you may not see any mangoes, but that doesn't mean that someone isn't
adding a mango concurrently, with the result that when both transactions
complete the database has two mangoes.

However, there is an old fashioned solution that works consistently. It's
called a unique index. If you never want more than one mango at a time,
put a unique index on fruit. If you try to insert a mango, you'll get an
error if there's a pre-existing or even concurrently created mango.

Good luck,

Ann


[Non-text portions of this message have been removed]