Try out this statement:



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

WHERE TBL_JOB_CARD_INVENTORY.INVENTORY_ID
NOT IN (select inventory_id from tbl_inventory)

The trick would be whether the subquery is run for each new row to be
inserted.



If it isn't, you may need to resort to a procedural method.



From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of dinol.softedge
Sent: Sunday, November 23, 2008 6:56 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Inserting only one row for each distinct id
(ignoring duplicates)



Hi

My problem is that I have a table that I am using to populate another.
The table is TBL_JOB_CARD_INVENTORY and I'm trying to populate
TBL_INVENTORY by finding all records in TBL_JOB_CARD_INVENTORY that do
not have matching records in TBL_INVENTORY. SO far I have come up
with the following statement. Problem is that some of the fields have
differing data for each unique Inventory_ID. I am happy just to take
the first instance of each record for each unique Inventory_ID. Any
help would be greatly appreciated. Many thanks

INSERT INTO
TBL_INVENTORY(
INVENTORY_ID,
STOCK_CODE,
DESCRIPTION,
COST_PRICE,
SELLING_PRICE,
INVENTORY_TYPE)
SELECT
DISTINCT TBL_JOB_CARD_INVENTORY.INVENTORY_ID,
TBL_JOB_CARD_INVENTORY.STOCK_CODE || ' (Deleted)',
TBL_JOB_CARD_INVENTORY.DESCRIPTION || ' (Deleted)',
0,
0,
TBL_JOB_CARD_INVENTORY.INVENTORY_TYPE
FROM
TBL_JOB_CARD_INVENTORY
LEFT OUTER JOIN TBL_INVENTORY ON
(TBL_JOB_CARD_INVENTORY.INVENTORY_ID = TBL_INVENTORY.INVENTORY_ID)
WHERE
TBL_INVENTORY.INVENTORY_ID IS NULL





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