需要修复合并更新查询（简单但仍然令人困惑）

发布于 2024-12-02 09:07:53 字数 371 浏览 3 评论 0原文

MERGE INTO table_1 a   
USING      
 (SELECT * from table_2) b  ON ( a.row_id = b.row_id and a.in_correct IS NULL) 
WHEN MATCHED THEN UPDATE SET a.in_correct = 'Y';

在上面的查询中 ORA-38104: ON 子句中引用的列无法更新。

我已经坐了几个小时来解决这个问题。

我已经确定问题出在 in_ Correct 字段上。

该字段“in_ Correct”不能同时放置在 ON 子句中和 SET 之后。但为了满足我的标准，我别无选择。

请帮帮我

原文

MERGE INTO table_1 a   
USING      
 (SELECT * from table_2) b  ON ( a.row_id = b.row_id and a.in_correct IS NULL) 
WHEN MATCHED THEN UPDATE SET a.in_correct = 'Y';

In the above query ORA-38104:Column referenced in ON clause cannot be updated.

I have been sitting for hours to resolve this.

I have identified that the problem is the field in_correct.

This field "in_correct" cannot be put in both ON clause and also after SET. But in order satisfy my criteria,I have no option.

Please help me out

分享到QQ

分享到微博

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

发布评论

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

柠北森屋 2024-12-09 09:07:53

MERGE INTO table_1 a USING
    (SELECT * from table_2) b 
ON ( a.row_id = b.row_id) 
WHEN MATCHED THEN UPDATE 
  SET a.in_correct = NVL(in_correct, 'Y');

更新：

一个更“通用”的命令（对于非空值）：

MERGE INTO table_1 a USING
    (SELECT * from table_2) b 
ON ( a.row_id = b.row_id) 
WHEN MATCHED THEN UPDATE 
  SET a.in_correct = case 
                 when in_correct = 'valuetobereplaced' then 'Y'; 
                 else in_correct; 
                 end;

MERGE INTO table_1 a USING
    (SELECT * from table_2) b 
ON ( a.row_id = b.row_id) 
WHEN MATCHED THEN UPDATE 
  SET a.in_correct = NVL(in_correct, 'Y');

UPDATE:

A more "general" command (for non null values):

MERGE INTO table_1 a USING
    (SELECT * from table_2) b 
ON ( a.row_id = b.row_id) 
WHEN MATCHED THEN UPDATE 
  SET a.in_correct = case 
                 when in_correct = 'valuetobereplaced' then 'Y'; 
                 else in_correct; 
                 end;

回复收藏 0 原文

~没有更多了~