删除与 LIKE 子字符串匹配的行？

发布于 2024-08-24 06:03:03 字数 468 浏览 9 评论 0原文

如何从表中删除行，其中某列包含子字符串，但该列的类型为“Long”。（是的，我知道我不应该使用 Long，但我正在维护别人的混乱）。

我的第一次尝试是：

delete from longtable 
  where search_long(rowid) like '%hello%';

^{（继这个答案。）}

这将返回：

SQL 错误：ORA-04091：表 blah.longtable 正在发生变化，触发器/函数可能看不到它

原文

How do you delete rows from a table, where a column contains a substring, but the type of that column is 'Long'. (Yes, I know I shouldn't use Long, but I'm maintaining someone else's mess).

My first attempt was:

delete from longtable 
  where search_long(rowid) like '%hello%';

^{(Following on from this answer.)}

This returns:

SQL Error: ORA-04091: table blah.longtable is mutating, trigger/function may not see it

分享到QQ

分享到微博

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

发布评论

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

对你的占有欲 2024-08-31 06:03:03

我刚刚复制了您的问题并得到了相同的错误 - 似乎该函数无法在 DELETE 语句中工作。错误的全文是：

ORA-04091: table HOU.LONGTABLE is mutating, trigger/function may not see it
ORA-06512: at "TONY.SEARCH_LONG", line 4

这种程序方法将起作用：

begin
  for r in (select id from longtable 
            where search_long(rowid) like '%hello%')
  loop
    delete longtable where id = r.id;
  end loop;
end;

I just replicated your problem and got the same error - it seems the function can't work from within a DELETE statement. The full text of the error is:

ORA-04091: table HOU.LONGTABLE is mutating, trigger/function may not see it
ORA-06512: at "TONY.SEARCH_LONG", line 4

This procedural approach will work:

begin
  for r in (select id from longtable 
            where search_long(rowid) like '%hello%')
  loop
    delete longtable where id = r.id;
  end loop;
end;

回复收藏 0 原文

~没有更多了~