| Subject | Re: [firebird-support] Inserting only one row for each distinct id (ignoring duplicates) |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-11-23T22:42:08Z |
At 01:55 AM 24/11/2008, you wrote:
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 )
The correlated subquery runs over a separate cursor to the main query, but the transaction can see any records it has inserted previously, so you will only insert on the first time the expression returns true.
This will *not* be a quick operation! I guess (or hope!) this is part of a one-off exercise to prepare these two tables for a referential relationship. ;-) You'll want the users off-line with all work committed before you start, so that you can do this with the database in a state where you can create the FOREIGN KEY constraint on TBL_JOB_CARD_INVENTORY.INVENTORY_ID safe in the knowledge that you won't get any violations to hold it up.
If you doing this normalisation in a script, make sure you commit the transaction that runs this statement before you attempt the ALTER TABLE .... ADD CONSTRAINT step. If you are doing it interactively in isql, remember that isql does not autocommit DML. If using other tools to do it interactively, bear in mind that many of them do not autocommit DDL either (as isql does by default).
./heLen
>HiTry this:
>
>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
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 )
The correlated subquery runs over a separate cursor to the main query, but the transaction can see any records it has inserted previously, so you will only insert on the first time the expression returns true.
This will *not* be a quick operation! I guess (or hope!) this is part of a one-off exercise to prepare these two tables for a referential relationship. ;-) You'll want the users off-line with all work committed before you start, so that you can do this with the database in a state where you can create the FOREIGN KEY constraint on TBL_JOB_CARD_INVENTORY.INVENTORY_ID safe in the knowledge that you won't get any violations to hold it up.
If you doing this normalisation in a script, make sure you commit the transaction that runs this statement before you attempt the ALTER TABLE .... ADD CONSTRAINT step. If you are doing it interactively in isql, remember that isql does not autocommit DML. If using other tools to do it interactively, bear in mind that many of them do not autocommit DDL either (as isql does by default).
./heLen