CODEWITHSUNDEEP
databaseoracle-databaseoracle10g
Prashobh
Prashobh

Reputation: 9542

How to insert a column to an existing table?

My table name userdetails

I want to insert a new column mob after location column.

I tried using the code

alter table userdetails 
add mob varchar2(10) after location;

but its showing error

ORA-01735: invalid ALTER TABLE option

Please help me.

I am using oracle10g.

Upvotes: 2

Views: 3943

Answers (2)

Jens A. Koch
Jens A. Koch

Reputation: 41737

There is no "after location". The syntax not valid.

You might go the following route: Just add the mob varchar to userdetails. It will be added at the end of the table. And you can still query it. ALTER TABLE userdetails ADD (mob varchar2(10))

To get the table structure you want:

// 1) rename the table
rename userdetails to userdetails_old;

// 2) recreate the table with your wanted structure
// Note that the selection order decides about the table structure.
create table userdetails
as
select a as a
     , b as b
     , location as location
     , mob  as mob
     , c as c
from userdetails_old;

// 3) check what you did
desc userdetails;

// 4) before dropping your old table
drop table userdetails_old;

Upvotes: 1

yital9
yital9

Reputation: 6702

try get rid of the "after"

alter table userdetails add ( mob varchar2(10) )

Upvotes: 4

Related Questions