Update Oracle table column with a sequence number based on varchar field in same table

Question

I am in need of help with the syntax to update an existing field in an oracle table with a new number sequence based on the ordering of another field that is VARCHAR2.

Current Table:

CODE    CODE_ORDER
A00     3
A00.0   4
A00.1   6
A00.9   8

Desired Outcome:

CODE    CODE_ORDER
A00     1
A00.0   2
A00.1   3
A00.9   4

The SELECT statement here gives me the desired results, but when I use it with an UPDATE statement, I receive an error.

UPDATE Table SET code_order = (select row_number() over (order by 
code asc) from Table)

The UPDATE statement displays this error:

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

Thank you in advance for any suggestions.

Popeye · Accepted Answer · 2019-08-28 17:20:55Z

2

You need to use subquery in SET clause as following:

UPDATE Table t SET t.code_order = (
Select tin1.rn from
(select row_number() over (order by tin.code asc) rn, tin.code from Table tin) tin1
Where t.code = tin1.code)

Note: thks query will work for sample data where there is no duplicate value exists in code column.

Cheers!!

answered Aug 28, 2019 at 17:20

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave Costa · Accepted Answer · 2019-08-28 17:10:20Z

1

You could do this:

merge
into table
using (
  select code,row_number() over (order by code asc) as new_code_order from table
) new_table
on (table.code = new_table.code)
when matched then update set
  table.code_order = new_table.new_code_order

I think this would also work in this simple case:

update (select * from table order by code) set code_order = rownum;

edited Aug 28, 2019 at 17:10

answered Aug 28, 2019 at 17:04

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

1 Comment

Spine Over a year ago

The MERGE works like a charm. Thank you very, very much!!

Barbaros Özhan · Accepted Answer · 2019-08-28 17:31:40Z

0

You can use with..as clause

update tab t
   set code_order = ( with tb as ( 
                      select t.*,row_number() over (order by code) as rn from tab t
                    )  
                    select rn
                      from tb
                     where tb.code=t.code)

considering the values for code column is unique

Demo

edited Aug 28, 2019 at 17:31

answered Aug 28, 2019 at 17:25

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Collectives™ on Stack Overflow

Update Oracle table column with a sequence number based on varchar field in same table

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related