asked on

ORACLE, database, 10.2.0.3, autokey update issue

folks

I have the following query which selects values from 1 table based on criteria and dumps it into another

DECLARE
v_SEED NUMBER;
BEGIN
SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
END;
this works fine on 1 record,though if I have two records then I get a unique constraint error,this occurs

as the below update isnt run

UPDATE AUTOKEY SET SEED =
(SELECT MAX(TICKETID) FROM
TICKET
) WHERE AUTOKEYNAME = 'SRID'

how could I integrate this between each row if insert so i elimiate duplicates?

r

Guy Hengel [angelIII / a3]

obviously, the seed field is primary key, so for the second insert, you need a new seed value...

DECLARE
v_SEED NUMBER;
BEGIN
SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
END;

or:

DECLARE
v_SEED NUMBER;
BEGIN
SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED+1,ASSETNUM FROM WORKORDER WHERE COST '100';
END;

Guy Hengel [angelIII / a3]

sorry, read only half of the question, my bad:

DECLARE
v_SEED NUMBER;
BEGIN
SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
UPDATE AUTOKEY SET SEED = (SELECT MAX(TICKETID) FROM TICKET )
WHERE AUTOKEYNAME = 'SRID';
END;

rutgermons

ASKER

still have the same issue when having more than 1 output record

any further advise?

brgds

r

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial