Filter
Top Products
Specifying the WHERE clause for update/delete
196 DataWindow .NET
Table 6-1: Specifying the WHERE clause for UPDATE and DELETE
Example
Consider this situation: a DataWindow object is updating the Employee table,
whose key is
Emp_ID; all columns in the table are updatable. Suppose the user
has 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 DataWindow
Designer 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.