2023年3月15日 20:17:12go评论66阅读模式

英文:

Oracle SQL: Massive update of multiple rows (also repeated rows)

问题

I have a tableX with two columns, some with repeated values like:

What i want to do is to update both columns and remain with only the digits part:

I thinked in this possibility(for only one column):

update tableX
   set column1 = (
       select regexp_replace(column1, '[^0-9]', '')
         from tableX);

But this code gives me ORA-01427

What's the other way to do this update?

英文:

I have a tableX with two columns, some with repeated values like:

What i want to do is to update both columns and remain with only the digits part:

I thinked in this possibility(for only one column):

update tableX
   set column1 = (
       select regexp_replace(column1, &#39;[^0-9]&#39;, &#39;&#39;)
         from tableX);

But this code gives me ORA-01427

What's the other way to do this update?

答案1

得分: 2

我觉得你想要

更新 表X
   设置 列1 = regexp_replace(列1, '&#39;[^0-9]&#39;', '&#39;&#39;');

ORA-01427 的原因是查询

       从 表X
       选择 regexp_replace(列1, '&#39;[^0-9]&#39;', '&#39;&#39;)

返回表X中的所有行。

英文:

I think you want

update tableX
   set column1 = regexp_replace(column1, &#39;[^0-9]&#39;, &#39;&#39;);

The ORA-01427 is caused because the query

       select regexp_replace(column1, &#39;[^0-9]&#39;, &#39;&#39;)
         from tableX

returns all rows from tableX.

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。