How to rename multiple columns in oracle using one Alter table statement?

Question

The only thing I found is renaming one column at a time:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

I read Oracle documentations, and couldn't get the answer for many columns at a time .

Ref: https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljrenamecolumnstatement.html

Why can't you just issue multiple statements? How are you picking which columnes to rename, and what to call them? (You've linked to the JavaDB docs; the Oracle RDBMS version [is here](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877); but you can still only rename one per `alter` statement.) — Alex Poole, Apr 18 '19 at 17:00
The SQL Standard (and, with that, Oracle DB and perhaps most if not all other SQL dialects) doesn't define an ALTER TABLE statement where you can change multiple column names in a single shot. So, you will have to write your own code (or find code already written by someone else) if you need to automate this type of action. WHY though? Why do you have to rename so many columns that doing them one at a time is not feasible for you? — , Apr 18 '19 at 17:16

score 5 · Answer 1 · edited Apr 19 '19 at 12:59

5

It is not possible to rename multiple table columns in a single command, as of Oracle 18c.

The Oracle 18c SQL Language Reference includes the below diagram to illustrate how the RENAME_COLUMN_CLAUSE of the ALTER TABLE command works. Unfortunately, almost every column property can be modified in groups, except for renaming.

edited Apr 19 '19 at 12:59

GolezTrol

114,394
18
182
210

answered Apr 19 '19 at 02:50

Jon Heller

34,999
6
74
132

score 1 · Answer 2 · answered Apr 18 '19 at 17:01

1

You can use user_tab_columns dictionary view as a data source within a cursor for a loop statement

declare
  v_table_name varchar2(40):='mytable';
begin
  for c in ( select from user_tab_columns where table_name = upper(v_table_name) )
  loop
    execute immediate ('ALTER TABLE '||c.table_name||' RENAME COLUMN '||c.column_name
                                                   ||' TO new_'||c.column_name);
  end loop;
end;

answered Apr 18 '19 at 17:01

Barbaros Özhan

59,113
10
31
55

I suspect "old_name" and "new_name" in the OP's post were generic - he may want to rename "baseball" to "MLB" and "basketball" to "NBA" for example (using US names). The solution you propose can be adapted for this more generic understanding of the problem, but it WILL be more complicated. – Apr 18 '19 at 17:18
@mathguy you may be right, but we have not enough clue about the renaming strategy. – Barbaros Özhan Apr 18 '19 at 17:42

score -1 · Answer 3 · edited May 02 '23 at 00:42

-1

ALTER TABLE table_name
RENAME COLUMN old_column_name1 TO new_column_name1,
RENAME COLUMN old_column_name2 TO new_column_name2,
...,
RENAME COLUMN old_column_nameN TO new_column_nameN;

edited May 02 '23 at 00:42

user16217248

3,119
19
19
37

answered Apr 30 '23 at 20:28

Abhinav

1
1

1

Are you sure that this works in Oracle? Others say you cant do it. – Rohit Gupta May 01 '23 at 03:50
1

On version 21c, this command raises the error "ORA-23290: This operation may not be combined with any other operation". – Jon Heller May 01 '23 at 13:35

How to rename multiple columns in oracle using one Alter table statement?

3 Answers3