[Solved] BASE - ALTER QUERY to ADD COLUMN

Discuss the database features
Post Reply
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

[Solved] BASE - ALTER QUERY to ADD COLUMN

Post by assa »

I have created a table "employee" with two fields "empid" and "empname". I have inserted some records in it. Now I want to add two more fields to same table say: "empaddress" and "empContactNo".
If I add them individually I can do it.
ALTER TABLE "emp" ADD COLUMN "empaddress" varchar(40);
and
ALTER TABLE "emp" ADD COLUMN "empContactNo" numeric(10);

But if I want to add two columns together then I get errors. My query is:
ALTER TABLE "emp" ADD (COLUMN "empaddress" varchar(40), COLUMN "empConatctNo" numeric(10));

My question is:
Can we add more than one column using alter table query in database?
Last edited by assa on Wed Jan 10, 2018 3:59 pm, edited 1 time in total.
OpenOffice 4.1.4 and Operating System Windows 8.1
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: BASE - ALTER QUERY to ADD COLUMN

Post by robleyd »

Does

Code: Select all

ALTER TABLE "emp" ADD ("empaddress" varchar(40), "empConatctNo" numeric(10));
do what you want? If not, please tell us the error you get.

You may not need the double quotes.
 Edit: Note that it helps us to have the error message any time you get one. 
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: BASE - ALTER QUERY to ADD COLUMN

Post by assa »

My error is :
1: Unexpected token: ( in statement [alter table "emp" add (]
OpenOffice 4.1.4 and Operating System Windows 8.1
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: BASE - ALTER QUERY to ADD COLUMN

Post by robleyd »

I suspect the syntax should not include the brackets () for HSQL

Code: Select all

ALTER TABLE "emp" ADD "empaddress" varchar(40), "empConatctNo" numeric(10);
http://www.hsqldb.org/doc/guide/ch09.ht ... le-section
 Edit: Possibly misleading link 
Last edited by robleyd on Mon Dec 18, 2017 1:41 am, edited 1 time in total.
Reason: Disable possibly misleading documentation link
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: BASE - ALTER QUERY to ADD COLUMN

Post by assa »

No it has the same error when i run the query:
ALTER TABLE "emp" ADD "empaddress" varchar(40), "empConatctNo" numeric(10);

Error:
1: Unexpected token: , in statement [,]
OpenOffice 4.1.4 and Operating System Windows 8.1
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: BASE - ALTER QUERY to ADD COLUMN

Post by Sliderule »

assa wrote:I have created a table "employee" with two fields "empid" and "empname". I have inserted some records in it. Now I want to add two more fields to same table say: "empaddress" and "empContactNo".
If I add them individually I can do it.
ALTER TABLE "emp" ADD COLUMN "empaddress" varchar(40);
and
ALTER TABLE "emp" ADD COLUMN "empContactNo" numeric(10);

But if I want to add two columns together then I get errors. My query is:
ALTER TABLE "emp" ADD (COLUMN "empaddress" varchar(40), COLUMN "empConatctNo" numeric(10));

My question is:
Can we add more than one column using alter table query in database?
Welcome to the OpenOffice / LibreOffice Base forum.

I will assume, you are using, as your database backend, either:
  1. HSQL 1.8.0.10, which is the Embedded database
  2. HSQL 2.4.0, the most recent version of HSQL as of the date I am writing this
This ( adding multiple columns to a table with ONE DDL ( Data Definition Language ) statement ) is something that HSQL does not support. But, as you have discovered, it can be accomplished using multiple DDL commands.

Just as an additional comment, with the words you used above, an ALTER statement is NOT a "table query", but rather a DDL statement. Therefore, it cannot be entered in in the Query section, but, rather using the Menu:Tools -> SQL...

I hope this helps, please be sure to let me / us know.

Sliderule
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: BASE - ALTER QUERY to ADD COLUMN

Post by Villeroy »

http://www.hsqldb.org/doc/1.8/guide/ch09.html is the documentation for the version of HSQL which is shipped with OpenOffice and LibreOffice.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: BASE - ALTER QUERY to ADD COLUMN

Post by assa »

Thanks. It was useful.
OpenOffice 4.1.4 and Operating System Windows 8.1
Post Reply