John Jason Jordan wrote:
I mistakenly entered "LQ1-," "LQ2-" and "LQ3-" in certain records in a
field to serve as query select markers. Each is followed by a two-digit
number for sorting, e.g., "LQ1-01, LQ1-02 ...." They should have been
"LQ1a-," "LQ1b-" and "LQ1c- followed by the two-digit number." Fixing
this with Edit > Find / Replace should be trivial, but I just started
using Base and I can't figure it out. Surely there is a simple way to
do this without manually typing a couple hundred changes.
I can open the table, and then Edit > Find allows me to locate the
records. But there is no Replace box.
I can open a query, but all I can figure out how to do is SELECT. How
do I do an UPDATE query?
Any suggestions?
You can always use the SQL window to enter any valid statement - Select,
Update, Delete or DDL command. In the case of HSQLdb database you will
find that it is reasonably compliant with the SQL standard(s).
Second a search of any of the web forums that support OpenOffice.org
users will bring you further examples of this operation it's covered in
other words - or just a search of the internet for SQL update statement
syntax.
Anyway - alas there is no GUI for this in Base you must use a text line
command - ..one way then is - not the only way
If you column is named Col_1 and your Table is Table_1 then first you
need to find the records ;
FIRST make a backup of your odb file - there is no undue command.
Open the SQL window Tools>SQL
Enter the update command ( or something similar anyway )
UPDATE "Table_1" ( "Col_1") VALUES ( 'LQ1a-' & RIGHT( "Col_1", LEN(
"Col_1") - LEN( 'LQ1-') )
WHERE "Col_1" LIKE 'LQ1-'%
Click the Execute button - The command is still in the window, just
replace LQ1- with new string in the different parts of the command.
A second way in your case would be to create an UPDATE command with a
case statement for the options - but not really worth the effort for a
one time shot, I would think.
You can find information on the different functions for stirngs, dates
and numerics at
http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures
HTH
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
