CODEWITHSUNDEEP
oracle-databaseplsqlddlalter
Amin
Amin

Reputation: 41

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

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

Upvotes: 4

Views: 18164

Answers (3)

Abhinav
Abhinav

Reputation: 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;

Upvotes: -1

Jon Heller
Jon Heller

Reputation: 36798

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.

enter image description here

Upvotes: 7

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

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;

Upvotes: 1

Related Questions