Specifications

Specifying the WHERE clause for update/delete
536 InfoMaker
Table 19-1: Specifying the WHERE clause for UPDATE and DELETE
Example
Consider this situation: a form is updating the Employee table, whose key is
Emp_ID; all columns in the table are updatable. Suppose you have changed the
salary of employee 1001 from $50,000 to $65,000. This is what happens with
the different settings for the
WHERE clause columns:
• If you choose Key Columns for the
WHERE clause, the UPDATE statement
looks like this:
UPDATE Employee
SET Salary = 65000
WHERE Emp_ID = 1001
Option Result
Key Columns The
WHERE clause includes the key columns only. These are the
columns you specified in the Unique Key Columns box.
The values in the originally retrieved key columns for the row are
compared against the key columns in the database. No other
comparisons are done. If the key values match, the update
succeeds.
Caution
Be very careful when using this option. If you tell InfoMaker only
to include the key columns in the
WHERE clause and someone else
modified the same row after you retrieved it, their changes will be
overwritten when you update the database (see the example
following this table).
Use this option only with a single-user database or if you are using
database locking. In other situations, choose one of the other two
options described in this table.
Key and
Updatable
Columns
The WHERE clause includes all key and updatable columns.
The values in the originally retrieved key columns and the
originally retrieved updatable columns are compared against the
values in the database. If any of the columns have changed in the
database since the row was retrieved, the update fails.
Key and
Modified
Columns
The
WHERE clause includes all key and modified columns.
The values in the originally retrieved key columns and the modified
columns are compared against the values in the database. If any of
the columns have changed in the database since the row was
retrieved, the update fails.