Oracle 9i (PL/SQL) 中的条件插入/更新

发布于 2024-11-08 20:21:11 字数 502 浏览 7 评论 0原文

我正在尝试根据某些条件构建一个查询来插入或更新/删除一行。我试图使用 MERGE 子句，但它有一些限制，不允许我更改某些字段。

这是代码：

MERGE INTO CADUSUNET t

USING (select 'FELIPE' as nomusunet from cademp where rownum = 1) v --generate the column and the value to compare
  ON (t.nomusunet = v.nomusunet)

WHEN MATCHED THEN
  UPDATE SET t.nomusunet = 'FELIPE BUENO' --I can't update a column that is referenced in the ON condition clause

WHEN NOT MATCHED THEN
  INSERT (nomusunet) VALUES ('FELIPE BUENO')

有办法做到这一点吗？

原文

I am trying to build a query to INSERT or UPDATE / DELETE a row depending on some conditions. I was trying to use the MERGE clause but it has some restrictions that doesn't let me change some fields.

Here is the code:

MERGE INTO CADUSUNET t

USING (select 'FELIPE' as nomusunet from cademp where rownum = 1) v --generate the column and the value to compare
  ON (t.nomusunet = v.nomusunet)

WHEN MATCHED THEN
  UPDATE SET t.nomusunet = 'FELIPE BUENO' --I can't update a column that is referenced in the ON condition clause

WHEN NOT MATCHED THEN
  INSERT (nomusunet) VALUES ('FELIPE BUENO')

Is there a way to do that?

分享到QQ

分享到微博

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

发布评论

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

放血 2024-11-15 20:21:11

你可以这样做：

begin
 update CADUSUNET t
 set t.nomusunet = 'FELIPE BUENO'
 where t.nomusunet = 'FELIPE';

 if sql%rowcount = 0 then
   INSERT INTO CADUSUNET (nomusunet) VALUES ('FELIPE BUENO');
 end if;
end;

You could do this:

begin
 update CADUSUNET t
 set t.nomusunet = 'FELIPE BUENO'
 where t.nomusunet = 'FELIPE';

 if sql%rowcount = 0 then
   INSERT INTO CADUSUNET (nomusunet) VALUES ('FELIPE BUENO');
 end if;
end;

回复收藏 0 原文

~没有更多了~