Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.You may find that the database which is part of LibreOffice/ OpenOffice delights you as much as it has me. This page tries to help you use it.
Remember that LibreOffice / OpenOffice, including the database manager, is free! But don't let that fool you. And it's not new. Big organizations, government, and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.
There's more about their database manager in the main index to this material.
This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus, or zero while the control key (ctrl) is held down will change the text's size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)
Page contents © TK Boyd, 3/21, Sheepdog Software
Getting started with SQL may feel like it is as much fun as getting started with swimming... but it IS worth it.
This short essay will provide an example.
I was building a new database to track my activity in the stock market.
There is table called "xact".
Each record records a transaction... some are the purchase of shares, others record the sale of shares.
Only four fields will cover our needs here...
That's enough for our needs
As I said, I was building a new database when I learned what I am passing on here. I had holdings already, in two different accounts.
(My database has an extra field I haven't mentioned... "brk" for "Broker"... i.e. where I hold the shares the record relates to.)
To get my new database started, I began by copying the information from statements for the two accounts.
The "frm" field would eventually show "st19c31" as the source of the shares into the database, as I built it.
"st" for (from) STatement, the "19c31" is a way to express 31 December 19 that may seem a little arcane, but has advantages, and is very easy to use once you get used to it.
These records are far from typical! Usually, as I said, the "frm" field holds "bou". But these records get the database up and running, reflecting my current holdings.
So! I sat down with my account statements, and from them copied how many shares/ what company into the database....
co shr -- --- aapl 20 bac 10 cost 10
A good start!
Now I wanted a quick way to put 'st19c31' in the "frm" field for every record.
Now.. changing the value in EVERY record's "frm" field should be too difficult. But, if I do this again one day, to fill the "frm" field for some new transactions, I will, of course, want to have the right characters for THOSE records put into the database, I will not want to change the frm-filed values there... I will only want to change the frm-field value of the new records. Start as you mean to go on, and all that.
No, this really isn't a digression. I just want to take you to the answer in two easily traversed stages.
All of this is written for users of Libre Office's database, with the Firebird embedded database engine. It was correct at 3/21, using Libre Office 4.0.3.1. Much of it should apply to any SQL based database.
The following command...
SELECT * FROM "xact" WHERE "shr"='10'
That will give rise to...
bac 10 cost 10
(The "where" in that is "where" as in "all cases that match this rule". It has nothing to do with my "where did I get the shares".)
The reason there are only two things on each line is that, at the moment, all of the other fields in the database are empty. The SELECT command shown actually "returned" 5 things per line... but 3 of them were empty.
The words in UPPER CASE are SQL commands. The rest are other things. The asterisk ("*") says you want all of the table's fields in the product of the SQL command. The "xact"specifies the table I want to "select" (use) records from. The "shr"='10' says I only want the records where the shr field has 10 in it.
(I'll tell you how to give your database a command in SQL in a moment!)
If you try my example, know that SQL, quite properly, is very fussy about punctuation. Where a quotation mark is shown an apostrophe won't do.
The command that lets us change what is in some (or all... if we wish...) records is UPDATE.
For the want described at the top of this, the correct command is....
UPDATE "xact" SET "frm" = 'st19c31' WHERE "frm" IS NULL
Which says.... UPDATE the table called xact. Make (they say "SET") the frm field of some records to st19c31. Which records should this happen to? The ones WHERE the frm field is currently empty.
Strictly speaking, NULL does NOT mean "empty". But in a new record, if nothing has been said about what should be in a field, that field IS NULL. There is no certainty about it's contents.
There's a good tutorial about what NULL means at https://www.firebirdsql.org/file/documentation/html/en/firebirddocs/nullguide/firebird-null-guide.html
That command should do what I wanted!
By the way... if later I wanted to change every entry that currently says "st19c31" to, I don't know, say "fred" for the sake of this example, the command would be...
UPDATE "xact" SET "frm" = 'fred' WHERE "frm"='st19c31'
N.B. It is WHERE "frm" IS NULL or WHERE "frm"='st19c31'. Use the "IS" syntax for ...IS NULL. (And for ...IS NOT NULL.)
Ah! I have been blithely saying "Use [this] command...", "use [that] command". How do you issue commands!
Libre Office leads you up the garden path. There's a "Create Query in SQL View..." option when you have "Queries" selected in the database manager's main window. THAT access to SQL is only for SELECT commands.
To use SQL more widely... which I strongly encourage you to master... you choose "Tools/ SQL..." from the database manager's main window's menu. That should give rise to what you should be seeing to the right of this, apart from the fact that when it opens the Command To Execute memo would normally be empty. I've shown the dialog with our UPDATE command typed in.
... and click "Execute". Certainly beats filling all those fields by hand! AND there won't be typos. Unless the same typo was inflicted on every record, in which case the problem is soon solved with another UPDATE.
That's it, for this subject, unless you have questions. (Contact details below, if you do.
I've shown how to selectively edit existing records. How can you use SQL to add records to a table? Ah.. that's a question for another day.
It would be really nice if people mentioned this page in geek forums, on social media, etc.
I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....
I am trying to present this material in a format that makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.
PLEASE >>> Click here to visit editor's Sheepdog Software freeware, shareware pages <<< PLEASE
If you liked this LibreOffice / OpenOffice database tutorial, see the main index for more information and help from the same author.
Editor's email address. Suggestions welcomed! Please cite fdb3chgmanyrecs.htm
Want a site hosted, or email? I like Ionos.com, once known as 1&1."
Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.
AND passes...
. . . . . P a g e . . . E n d s . . . . .