I have a database with a table ‘annual_cultivation’ that has about 5 or 6 table views that depend on it. I want to edit the table using a calc spreadsheet because hundreds of records need to be standardized, for example I have 200 records that say ‘sew seed’ and 200 that say 'sew ‘seeds’ and i want them all to say ‘sow seeds’. Normally to edit a table I would copy it and paste to a spreadsheet, make the changes, delete the original from the database and then select all and drag from the spreadsheet to base. Then I would go through the process of assigning the correct data types like integer or varchar or it doesn’t work. I’m realizing to do this process I would have to delete all of the table views that depend on it and it seems like a lot of work to recreate them all.
Is there an easier way?
menu:Tools>SQL…
UPDATE "table name" SET "column name" = 'sew seeds' WHERE "column name" = 'sew seed'
[replace “column name” and “table name” with the actual names of column and table]
menu:View>Refresh Tables
In a relational database, each name of each item should be stored only once.
Demo database: https://forum.openoffice.org/en/forum/download/file.php?id=11250 with names of persons, items and animals.
Slightly OT but isn’t sow what you do to seeds and sew involves a needle and thread?
Whatever he does, it may be translated from a language other than English. It’s just about strings in fields.
you’re right
I think you have demonstrated how to rename columns. I am not needing to rename columns. I am needing to use the spreadsheet to order by column ‘practice’ and then decide what standard I will use for representing the dozens of tasks necessary for describing cultivation practices. Instead of having ‘sew seeds’, Sew Seeds’, Sow Seed’, Sow seeds’, ‘sow seeds’ I would have ‘sow seeds’ and instead of having ‘Divide roots’ divide roots’, Divide Roots… I would have only ‘divide roots’
“column name” is a place holder for your actual column name to be updated with new values.
Are you suggesting I make a table for the diversity of ‘practices’ with a primary key like
ID, practice
1, sow seeds
2, divide roots
and replace text with ID #base
If I were to do that I would want to do it in a spreadsheet so I could order the entries by ‘practice’ and then replace with copy & paste.
My question is if there is a way to do this without needing to delete all of the table views that depend on the table so that I can copy the date to a spreadsheet, delete the table, edit the data and then paste it back into base.
Of course, you can manipulate databases without the help of spreadsheets.
One-to-Many-SQL.odt (42.1 KB)
Follow the instructions.
The resulting SQL code generates the table and modifies your existing table accordingly.
But spreadsheets can be a nice grid interface, especially if input requires a lot of calculations and support. They can also be a useful report receptacle when many outputs are generated.
Your database must not store values that have been calculated. Store raw data only in a database. Then you can calculate everything in the tool of your choice which is able to load the database data (if not directly by the database itself which can be much more comfotable than sheets). Strict separation of data and calculation is a clear advantage of a database.
Anyway:
Create an empty copy of the database table:
- Copy the original table icon (do not open the table)
- Paste. In the upcoming dialog, enter the new table name and choose “Definition only”.
- Copy the rows you want to edit into the spreadsheet. A copied table icon copies the entire table, a query icon copies the returned query data, any selection of rows copies the selected rows. An icon dragged from the data source window into a sheet, creates a link to the record set.
- Do whatever you need to do without violating the rules of your database (wrong column types, missing values, key violations, duplicates).
- Copy the edited rows back into the blank table. If this raises some error, you violated the rules while editing the spreadsheet (which is very common).
- Adjust the names in the following statement and run:
UPDATE "Table"
SET ("This", "That", "Other", "Stuff")
=(SELECT "This", "That", "Other", "Stuff"
FROM "Temporary_Table"
WHERE "Table"."ID"="Temporary_Table"."ID")
Important: “ID” is the primary key which identifies each record. Each record in the original “Table” is updated with the column values from “Temporary_Table” where the “ID” is the same.
If this has been executed sucessfully, don’t forget to clear the temp. table:
DELETE FROM "Temporary_Table"