Specifying the WHERE clause for update/delete

Sometimes multiple users access the same tables at the same time. In this type of situation, you need to decide when to allow your application to update the database. If you allow your application to always update the database, it could overwrite changes made by other users.

Figure 20-4: Controlling when updates succeed

The sample shows a portion of the Specify Update Properties dialog box. A group box with three radio buttons is titled Where Clause for Update / Delete. The radio buttons offer the following choices: Key Columns, Key and Updatable Columns, and Key and Modified Columns. In the sample, the button for Key and Updatable Columns is selected.

You can control when updates succeed by specifying which columns PocketBuilder includes in the WHERE clause in the UPDATE or DELETE statement used to update the database:

UPDATE table...
SET column = newvalue
WHERE col1 = value1
AND col2 = value2 ...

DELETE
FROM table
WHERE col1 = value1
AND col2 = value2 ...

Using timestamps

SQL Anywhere lets you create special timestamp columns so that you can ensure that users are working with the most current data. If the SELECT statement for the DataWindow object contains a timestamp column, PocketBuilder includes the key column and the timestamp column in the WHERE clause for an UPDATE or DELETE statement regardless of which columns you specify in the Where Clause for Update/Delete box.

If the value in the timestamp column changes (possibly because another user modifies the row), the update fails.

Meanings of WHERE clause options

Choose one of the options in Table 20-1 in the Where Clause for Update/Delete box. The results are illustrated by an example following the table.

**Table 20-1: Specifying the WHERE clause for UPDATE and DELETE**
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 PocketBuilder to include only the key columns in the WHERE clause and someone else modifies the same row after you retrieve it, those modifications will be overwritten when you update the database. 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.

Example

Consider this situation: a DataWindow object is updating the Employee table, whose key is Emp_ID; all columns in the table are updatable. If the user changes 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 selected the Key Columns option for the WHERE clause, the UPDATE statement looks like this:
```
UPDATE Employee
SET Salary = 65000
WHERE Emp_ID = 1001
```
This statement will succeed whether or not other users have modified the row since your application retrieved it. For example, if another user has modified the salary to $70,000, that change will be overwritten when your application updates the database.
If you selected Key and Modified Columns for the WHERE clause, the UPDATE statement looks like this:
```
UPDATE Employee
SET Salary = 65000
WHERE Emp_ID = 1001	
   AND Salary = 50000
```
Here the UPDATE statement also checks the original value of the modified column in the WHERE clause. The statement will fail if another user has changed the salary of employee 1001 since your application retrieved the row.
If you selected Key and Updatable Columns for the WHERE clause, the UPDATE statement looks like this:
```
UPDATE Employee
SET Salary = 65000
WHERE Emp_ID = 1001	
   AND Salary = 50000	
   AND Emp_Fname = original_value	
   AND Emp_Lname = original_value	
   AND Status = original_value	
   ...
```
Here the UPDATE statement checks all updatable columns in the WHERE clause. This statement will fail if any of the updatable columns for employee 1001 have been changed since your application retrieved the row.