Oracle DB insert query fixing

Question

I have an oracle query that I'm attempting to correct - it is throwing this error because, I'm guessing, the ids that are referenced in it don't exist in a QA db, but do exist in Production:

Error executing INSERT statement. ORA-02291: integrity constraint (EFAPP.REFMETRIC603) violated - parent key not found

and my broken query is this one:

insert into IDMAPPING (metricid, storeid) values (50441, 18198) 
    WHERE EXISTS (SELECT * FROM METRIC WHERE METRICTID = 50441) and WHERE EXISTS (SELECT * FROM STORE WHERE  STOREID = 18198);

Basically, what I'm trying to do is to wrap insert statement into something that would allow for the insert when the ids actually exist. Would love some help on fixing this query.

@PM77-1 Thanks so much! very much appreciate the good pointer. — user2917629, Jul 01 '22 at 18:26

MT0 · Answer 1 · 2022-07-01T18:30:26.410

Use INSERT ... SELECT and CROSS JOIN the tables:

INSERT INTO IDMAPPING (metricid, storeid)
SELECT m.metrictid, s.storeid
FROM   METRIC m
       CROSS JOIN store s
WHERE  m.metrictid = 50441
AND    s.storeid = 18198;

Or just use PL/SQL and catch the error:

DECLARE
  parent_key_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(parent_key_not_found, -2291);
BEGIN
  INSERT INTO idmapping (metricid, storeid) VALUES (50441, 18198);
EXCEPTION
  WHEN parent_key_not_found THEN
    -- Ignore the exception
    NULL;
END;
/

db<>fiddle here

Oracle DB insert query fixing

1 Answers1