The Select and Update command updates multiple rows in a database based on conditions specified in “where” clauses. A select statement retrieves data from a database and returns it in the form of query results. (When using this command, any fields/columns that have the auto-increment flag checked are not updated. The auto-increment flag is set on the Fields tab of the New Field or Field Properties window. See Adding a New Field in the Records/Tables chapter for instructions on setting this attribute.)
When you select the up arrow next to the Select and Update text box, the Select and Update FROM Application Record window displays.
SQL User File – the internal ECMap name that provides the link to the database containing the rows/records that are updated.
SQL User Record – the internal ECMap name for the specific rows/records/tables (in the file/database specified above) that are updated.
SQL Table Name – the name of the database table that corresponds to the internal ECMap file name. This is the actual database table that is updated.
You can use one or more where clauses to specify conditions. On each Where Clause tab, enter the following:
Record Field – the application record field that holds the value that is used as the left side of the conditional statement defining which rows in the database are updated. If you choose Select, the Records/Tables window displays. Double-click a field or drag it and drop it onto the Record Field text box.
Operator – logical operator that relates the left and right sides of the conditional statement defining which rows in the database are updated. The choices in the drop-down list include:
EQ – equal to.
NE – not equal to.
GT – greater than.
GE – greater than or equal to.
LT – less than.
LE – less than or equal to.
Variable – holds the value that is used as the right side of the conditional statement defining which rows in the database are updated. Choose from:
Memory Variable – appears in the first text box, and the Memory Variables window displays next to the Select and Update FROM Application Record window. Double-click a memory variable and the program automatically enters it in the second text box.
Record/Field – appears in the first text box, and the Records/Tables window displays next to the Select and Update FROM Application Record window. Double-click a field and the program automatically enters it in the second text box.
String Constant – appears in the first text box, and the WinMap window displays next to the Select and Update FROM Application Record window. Enter a value in the text box on the WinMap window and select OK. The value is automatically entered in the second text box.
When you finish entering all the information, select Compile Statement and the program constructs the SQL statement in the text box at the bottom of the window.
Select Test Statement to make sure that the program can connect with the database to execute the statement.
