Yes, but it makes me question your design.

Suppose you have three records in TBL_JOB_CARD_INVENTORY:

INVENTORY_ID STOCK_ID
1 1
2 2
2 1

If the last record was inserted into TBL_INVENTORY, then both of the others would be denied. If one of the first two was inserted, the other amongst those two could also be inserted. Hence, you would end up with a different number of records depending on which record happened to be inserted first.

Of course, I don't know whether this can be a problem in your particular case or not.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of dinol.softedge
Sent: 24. november 2008 11:52
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Inserting only one row for each distinct id (ignoring duplicates)

One more question. If I reenable the unique on stock_code would the
correct statement look like this to achieve the same thing?

INSERT INTO TBL_INVENTORY(
INVENTORY_ID,
STOCK_CODE,
DESCRIPTION,
COST_PRICE,
SELLING_PRICE,
INVENTORY_TYPE)
SELECT
tgic.INVENTORY_ID,
tgic.STOCK_CODE || ' (Deleted)',
tgic.DESCRIPTION || ' (Deleted)',
0,
0,
tgic.INVENTORY_TYPE

FROM TBL_JOB_CARD_INVENTORY tgic
where not exists (
select 1 from TBL_INVENTORY ti
where ti.INVENTORY_ID = tgic.INVENTORY_ID
OR ti.STOCK_CODE = tgic.STOCK_CODE )