HearAid Version 5.0

How do I "Search and Replace" values in a table?

How do I "Search and Replace" values in a table?

Previous topic Next topic  

How do I "Search and Replace" values in a table?

Previous topic Next topic  

There are occasions where you may need to search for particular values in a table and replace them with a new value. Batch processing of this kind is best performed using an SQL script in the Query window.

As a security and safety precaution, HearAid will only allow the UPDATE, INSERT and DELETE SQL procedures to be performed by the SYSDBA, so if you are not logged on to HearAid as SYSDBA or have Administrator privileges you will not be able to carry out a Search and Replace!

Searching and replacing uses the UPDATE command (There is a sample SQL script called "Search and Replace Example.sql" in the ..\Reports\SQL folder):

UPDATE tablename

SET fieldname = newvalue

WHERE fieldname = oldvalue

 

To carry out a "search and replace" you must know:

1.The name of the table you want to alter ("tablename" in the UPDATE clause")
2.The name of the field containing the value you want to update ("fieldname" in the SET and WHERE clauses)
3.Then new value of the field ("newvalue" in the SET clause)
4.The old field value that you want to replace ("oldvalue" in the WHERE clause)

 

To execute the search and replace:

1.Modify the script by substituting "tablename", "fieldname", "newvalue" and "oldvalue" as appropriate
2.Click the Execute SQL button

 

The process is usually very fast!

 

Beware! There is no "Undo" function! If you make an error you will need to execute another SQL script to revert to the original value:

 

UPDATE tablename

SET fieldname = oldvalue

WHERE fieldname = newvalue [AND updated >= datetime first search/replace occurred]

 

If you run into trouble, please contact HearSoft Pty Ltd BEFORE you attempt to undo your changes!