OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

Add a new field to an existing table with a default value

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
d4rksh4rk
General User
General User


Joined: 08 Jul 2009
Posts: 16
PostPosted: Fri Jun 18, 2010 1:20 am    Post subject: Add a new field to an existing table with a default value Reply with quote
Hi,
i need to add a new field (tinyint) to an existing table.
I need that for all the existing rows in the table the value of the new field is 1, but for the new rows the number could be different (so 1 should not be the default value).

i've read that if i create the new field and then run this code in Tools->SQL ecc

alter table mytable alter column mycolumn set default 1;

i should set the value 1 to all the selected column fields, but when i try the code Base, i get a table not found error.

I've tried this code
alter table "mytable" alter column "mycolumn" set default 1;
and this time the command has been executed, but the value of the new field in the existing row is still null and even in the new rows the filed is empty...

any suggestion?
Back to top
View user's profile Send private message
FreewayFred
Power User
Power User


Joined: 22 Feb 2007
Posts: 85
Location: Wisconsin USA
PostPosted: Fri Jun 18, 2010 4:44 am    Post subject: Reply with quote
The following

Quote:
alter table "mytable" alter column "mycolumn" set default 1;


would, based on my experience, supply the default value to future entries to the table. I use code like this to avoid entering data, like dates, that will be the same for all the entries in a batch of entries.
Back to top
View user's profile Send private message
d4rksh4rk
General User
General User


Joined: 08 Jul 2009
Posts: 16
PostPosted: Fri Jun 18, 2010 5:25 am    Post subject: Reply with quote
So i still need to find a way to write the new field for all the old rows of my table...
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2474
Location: 3rd Rock From The Sun
PostPosted: Fri Jun 18, 2010 6:47 am    Post subject: Reply with quote
d4rksh4rk:

You said:
d4rksh4rk wrote:
So i still need to find a way to write the new field for all the old rows of my table...


Therefore, you do NOT want to use a DEFAULT command. But, yes, it can 'easily' be accomplished . . . despite all the words below. It should take about 60 seconds, or less . . . alot more time to read it compared to doing it. Razz

OK, to explain the process in English first . . . to be followed by 'code' . . . this is can accomplished 'easily'. Smile

First, need to ALTER ( change ) your current table ( in the example below I will assume a table name of "MyTable" ) . . . change it to your real table name ) to include this new field ( in the example below I will assume a field name of "MyField" ) . . . next will UPDATE the contents of "MyField" to a value of 1.

How to accomplish the task.
  1. Make a backup copy of your OpenOffice database file ( *.odb ), just in case. Doubtful you will need the 'backup', but, just in case a good SAFETY procedure to follow Smile
  2. Open your OpenOffice database file ( *.odb )
  3. Two ways to ALTER your table to add the field, from the GUI ( Graphics User Interface ) or with direct SQL commands EITHER

    1. From the GUI:
      1. Click on Tables on the left
      2. Right click on your the table name you want to edit and from the drop-down menu, select Edit
      3. Under Field Name, enter the new field name of "MyField"
      4. Under Field Type, select Tiny Integer [ TINYINT ]
      5. Click on Save icon on the Toolbar

    2. Direct entry via SQL
      1. From the Menu: Tools -> SQL...
      2. Enter the following code in the Command to execute box ( change "MyTable" and "MyField" to the correct names in your database ):

        Code:
        ALTER TABLE "MyTable" Add Column "MyField" TINYINT

      3. Press the Execute button
      4. Command successfully executed should be displayed
      5. Press the Close button

  4. Now, to UPDATE all of your CURRENT records to a value of 1
    1. From the Menu: Tools -> SQL...
    2. Enter the following code in the Command to execute box ( change "MyTable" and "MyField" to the correct names in your database ):

      Code:
      UPDATE "MyTable" SET "MyField" = 1

    3. Press the Execute button
    4. Command successfully executed should be displayed
    5. Press the Close button

Remember to change "MyTable" to your real table name, and "MyField" to your real field name. Smile The above may seem like alot of steps, but, I was just attempting to explain it to you, and, OTHERS, that may read this, with different levels of computer experience. Smile

For detailed documentation on ALTER and UPDATE commands with HSQL, see:
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group