How to specify the WHERE clause for UPDATE / DELETE ?

Sometimes multiple users are accessing the same tables at the same time. In these situations, 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. You can control when updates succeed by specifying which columns are included in the WHERE clause of an UPDATE or DELETE statement.

UPDATE table ...
SET column = newvalue
WHERE coll = valuel
AND col2 = value2

DELETE
FROM table
WHERE coll = valuel
AND col2 = value2 ...

Choose one of the following in the Where Clause for Update / Delete. The results are illustrated by an example following the table.

Option	Result
Key Columns	The WHERE clause includes the key columns only (these are the columns you specified as the Unique or Primary Key of the table. 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 someone else modified the same row after you retrieved it, their changes 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 Updateable 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.

Consider this situation: Your application is updating the EMPLOYEE table, whose key is EMP-ID; all columns in the table are updatable. Say your user has changed the salary of employee 1001 from CHF 50,000 to CHF 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

This statement will succeed regardless of whether other users have modified the row since your application retrieved the row. For example, if another user had modified the salary to CHF 70,000, that change will be overwritten when your application updates the database.

If you choose 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 is also checking the original value of the modified column in the WHERE clause. The statement will fail if another user changed the salary of employee 1001 since your application retrieved the row.

If you choose Key and Updateable Columns for the WHERE clause,
the UPDATE statement looks like this:

UPDATE employee
   SET salary = 65000
WHERE emp-id = 1001
   AND salary = 50000
   AND fname = original-value
   AND lname = original value
   AND status = original value;

Here the UPDATE statement is checking 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.