Oracle DB插入查询修复

发布于 2025-02-12 10:33:01 字数 508 浏览 0 评论 0 原文

我有一个我试图纠正的Oracle查询 - 它正在抛出此错误，因为，我猜想，其中所引用的ID在QA DB中不存在，但确实存在于生产中：

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

而我的损坏了查询是这个问题：

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);

基本上，我要做的是将插入语句插入允许IDS实际存在时插入的内容中。很乐意解决此查询。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

葬心 2025-02-19 10:33:01

使用插入...选择和交叉加入表：

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;

或只使用PL/SQL并捕获错误：

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＆lt;＆gt; fiddle 在这里

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

回复收藏 0 原文

~没有更多了~