[Solved] Insert new field between existing fields

Discuss the database features
Post Reply
HCallahan
Posts: 4
Joined: Fri Nov 09, 2018 4:18 pm

[Solved] Insert new field between existing fields

Post by HCallahan »

I've looked everywhere. I have created a table, entered data, but forgot to put the zip code field in. I need to put the Zip field in after the state and before phone number. I've tried everything except creating a new table. Can this be done?
Last edited by HCallahan on Fri Nov 09, 2018 7:00 pm, edited 1 time in total.
OpenOffice 4.1.5 on Mac Mojave
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: insert new field between existing fields

Post by eremmel »

For a database the location of the columns are not important. Just add it to the end. Use SELECT statement to query the table and put the fields in the right order.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
HCallahan
Posts: 4
Joined: Fri Nov 09, 2018 4:18 pm

Re: insert new field between existing fields

Post by HCallahan »

I new to doing this. Are you saying that once I do the query the order will be changed in the table the next time I access?
OpenOffice 4.1.5 on Mac Mojave
HCallahan
Posts: 4
Joined: Fri Nov 09, 2018 4:18 pm

Re: insert new field between existing fields

Post by HCallahan »

I tried adding "zip code" to the database and it tells me I cannot quit and save because "zip code" already exists, which it doesn't. I don't see SELECT. Where is it located?
OpenOffice 4.1.5 on Mac Mojave
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: insert new field between existing fields

Post by Sliderule »

HCallahan wrote:I've looked everywhere. I have created a table, entered data, but forgot to put the zip code field in. I need to put the Zip field in after the state and before phone number. I've tried everything except creating a new table. Can this be done?
One way to accomplish what you want is:

Assuming you want to add a new column ZIP to your table "My_Table" before the column "Phone_Number" . . . you should follow these steps:
  1. Open your Open Office / Libre Office Base file ( *.odb )
  2. From the Menu: Tools -> SQL...
  3. In the Command to execute box, enter:

    Code: Select all

    ALTER TABLE "My_Table" ADD COLUMN "ZIP" VARCHAR(5) BEFORE "Phone_Number"
  4. Press the Execute button
  5. Press the Close button
  6. From the Menu: View -> Refresh Tables
  7. Smile and say: "That was easy, now all I have to do is let the forum know the issue is resolved.
Explanation:
  1. The above SQL command will add the new column with the defined type ( for example VARCHAR(5) prior to the column "Phone_Number" .
  2. It is CRITICAL that each item between double quotes are defined exactly the way you have defined them in your database, including CASE: UPPER, Mixed, lower. For example, mixed case, "Phone_Number" is NOT the same as UPPER case "PHONE_NUMBER".
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
HCallahan
Posts: 4
Joined: Fri Nov 09, 2018 4:18 pm

Re: insert new field between existing fields - SOLVED!!!

Post by HCallahan »

Sliderule THANK YOU
OpenOffice 4.1.5 on Mac Mojave
Post Reply