Altering data types within a table using SQL command ALTER

Question

I have researched how to alter table data types and I understand how to do it but I cannot get it to work. I am trying to update my table Person within APP using the following command:

ALTER TABLE APP.PERSON ALTER uName varchar;

What I have tried so far:

using Modify but realised that, after I received errors, this should indeed be ALTER.
changing uName to UNAME.
changing it to a data type of varchar2.
adding in the size of the data type '(20)' and 'NOT NULL' at the end.

Where am I going wrong? I am using Netbeans 7.3 Beta 2 running on Mac OS X, this is all being done within the SQL Commands section of Netbeans, using Java-DB as my database if any of that matters.

Community · Accepted Answer · 2017-05-23 12:05:40Z

2

It has already been answered here on SO. You basically need to create new column with desired datatype and delete the old one. However, if you take a look into Apache Derby doc, there is a SET DATA TYPE command, so try something like

ALTER TABLE APP.PERSON ALTER UNAME SET DATA TYPE VARCHAR(30)

EDIT

If code above doesn't work, then you just have to recreate the column as I suggested before.

ALTER TABLE APP.PERSON ADD COLUMN UNAME_NEW VARCHAR(30);
UPDATE APP.PERSON SET UNAME_NEW = UNAME;
ALTER TABLE APP.PERSON DROP COLUMN UNAME;
RENAME COLUMN APP.PERSON.UNAME_NEW TO UNAME;

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Jan 28, 2013 at 20:05

Petr Mensik

27.7k17 gold badges94 silver badges119 bronze badges

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

5 Comments

John Vasiliou Over a year ago

This seems to be the command I need to use but I can't get it working, using @Joachim's response I receive this error - "Error code -1, SQL state 42Z15: Invalid type specified for column 'UNAME'. The type of a column may not be changed."

John Vasiliou Over a year ago

Same error - "Error code -1, SQL state 42Z15: Invalid type specified for column 'UNAME'. The type of a column may not be changed." ... that's with and without an ending semi-colon.

Petr Mensik Over a year ago

I don't have a running Derby to test it, so you can just fall back to original solution.

John Vasiliou Over a year ago

OK, thanks, one last question if I may. I understand you don't have a running Derby to test this but you may know, how do I put a column in a specific position so I can replace it's old position?

Petr Mensik Over a year ago

I am sorry, I don't know this one. Quick look to documentation didn't tell me anything about this:(

Eric J. · Accepted Answer · 2013-01-28 20:03:23Z

1

Most databases require specific permissions to use DDL (Data Definition Language) commands like ALTER TABLE. Very often, the DB credentials used in an application tier do not have DDL permissions in the database.

Verify that the connection you are using has permission to run ALTER TABLE. If indeed it does, post the specific code you are using and any specific error messages.

answered Jan 28, 2013 at 20:03

Eric J.

151k65 gold badges353 silver badges563 bronze badges

Comments

Joachim Isaksson · Accepted Answer · 2013-01-28 20:08:43Z

1

I don't have JavaDB to test on, but according to the documentation it should be;

ALTER TABLE APP.PERSON ALTER uName SET DATA TYPE VARCHAR(20)

answered Jan 28, 2013 at 20:08

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

4 Comments

John Vasiliou Over a year ago

Thanks, I literally just tried this and came back to post my results then saw your post. I am receiving this error - "Error code -1, SQL state 42Z15: Invalid type specified for column 'UNAME'. The type of a column may not be changed."

Joachim Isaksson Over a year ago

@JohnVasiliou Which version of JavaDB are you running?

John Vasiliou Over a year ago

I'm not sure, how can I check? It's an embeddedDriver if that helps? It was shipped with Java 6 I believe.

Joachim Isaksson Over a year ago

@JohnVasiliou I suspect it's too old to have the SET DATA TYPE command, in which case @petr's solution of recreating the column is your only option.

Collectives™ on Stack Overflow

Altering data types within a table using SQL command ALTER

3 Answers 3

5 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related