使用 Oracle 中的 Join 查询进行更新

发布于 2024-11-25 03:01:58 字数 242 浏览 2 评论 0原文

查询有什么问题？（它无限期地执行）

UPDATE table1 t1 SET (t1.col,t1.Output) = (
  SELECT t2.col, t3.Output + t2.col
  FROM tabl2 t3 
  LEFT JOIN table1 t2 ON t3.Join_Key = t2.Join_Key
  WHERE t2.col is not NULL);

请帮助我。

原文

what is wrong in query? (it executes indefinitely)

UPDATE table1 t1 SET (t1.col,t1.Output) = (
  SELECT t2.col, t3.Output + t2.col
  FROM tabl2 t3 
  LEFT JOIN table1 t2 ON t3.Join_Key = t2.Join_Key
  WHERE t2.col is not NULL);

Please, help me.

分享到QQ

分享到微博

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

发布评论

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

夏日浅笑〃 2024-12-02 03:01:58

除非您的 SELECT 子查询返回单行，否则您的 UPDATE 语句应该失败并出现错误：

ORA-01427: single-row subquery returns more than one row

一般来说，如果您有相关更新，则需要一些条件来关联外部中的行table T1 到内部子查询中的行，以确保子查询返回单行。这通常看起来像

UPDATE table1 t1 SET (t1.col,t1.Output) = (
  SELECT t2.col, t3.Output + t2.col
  FROM tabl2 t3 
  LEFT JOIN table1 t2 ON t3.Join_Key = t2.Join_Key
  WHERE t2.col is not NULL
    AND t1.some_key = t2.some_key);

最后，这个 UPDATE 语句正在更新 T1 中的每一行。这就是你的意图吗？或者您只想更新在子查询中找到匹配项的行？

Unless your SELECT subquery returns a single row, your UPDATE statement should fail with the error

ORA-01427: single-row subquery returns more than one row

Generally, whey you have a correlated update, you need some condition that relates rows in the outer table T1 to rows in the inner subquery in order to ensure that the subquery returns a single row. That would generally look something like

UPDATE table1 t1 SET (t1.col,t1.Output) = (
  SELECT t2.col, t3.Output + t2.col
  FROM tabl2 t3 
  LEFT JOIN table1 t2 ON t3.Join_Key = t2.Join_Key
  WHERE t2.col is not NULL
    AND t1.some_key = t2.some_key);

Finally, this UPDATE statement is updating every row in T1. Is that what you intend? Or do you only want to update the rows where, for example, you find a match in your subquery?

回复收藏 0 原文

无法言说的痛 2024-12-02 03:01:58

您的查询对于通用 table1、table2 和 join_key 引用没有多大意义。

如果这不是您正在寻找的结果，那么拥有一些示例数据将有助于更好地了解您正在寻找的结果。

update table1 t1
   set t1.col = (select t2.col
                 from table2 t2
                 where  t1.join_key = t2.join_key(+)  
                  and  t1.col is not null),
       t1.output = (select t2.output + t1.col
                    from  table2 t2
                   where  t1.join_key = t2.join_key(+)  
                     and  t1.col is not null);

Your query does not make a whole lot of sense with the generic table1, table2, and join_key references.

If this is not what you are looking for, it would be helpful to have some sample data to get a better idea of what results you are looking for.

update table1 t1
   set t1.col = (select t2.col
                 from table2 t2
                 where  t1.join_key = t2.join_key(+)  
                  and  t1.col is not null),
       t1.output = (select t2.output + t1.col
                    from  table2 t2
                   where  t1.join_key = t2.join_key(+)  
                     and  t1.col is not null);

回复收藏 0 原文

~没有更多了~