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

Question

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

column1	column2
'1000 -- Some Text'	'2000 -- Some Text'
'1000 -- Some Text'	'2000 -- Some Text'
'1001 -- Some Text'	'2001 -- Some Text'
'1002 -- Some Text'	'2002 -- Some Text'

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

column1	column2
1000	2000
1000	2000
1001	2001
1002	2002

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?

Answer 1

I think you want

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

The ORA-01427 is caused because the query

       select regexp_replace(column1, '[^0-9]', '')
         from tableX

returns all rows from tableX.