Specifying 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 PowerBuilder includes
in the WHERE clause in the UPDATE or DELETE statement
used to update the database:

Using timestamps Some DBMSs maintain timestamps so you can ensure that users are
working with the most current data. If the SELECT statement
for the DataWindow object contains a timestamp column, PowerBuilder 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 due
to another user modifying the row), the update fails.
To see whether you can use timestamps with
your DBMS, see Connecting to Your Database
.

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.

Example

Consider this situation: a DataWindow object is updating the Employee table, whose
key is Emp_ID; all columns in the
table are updatable. Say 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:
  PowerBuilder

  UPDATE Employee<br />SET Salary = 65000<br />WHERE Emp_ID = 1001

  1	UPDATE Employee<br />SET Salary = 65000<br />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 $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:
  PowerBuilder

  UPDATE Employee<br />SET Salary = 65000<br />WHERE Emp_ID = 1001<i> <br /> AND Salary = 50000</i>

  1	UPDATE Employee<br />SET Salary = 65000<br />WHERE Emp_ID = 1001<i>    <br />   AND Salary = 50000</i>

  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 Updatable Columns for the WHERE clause,
  the UPDATE statement looks like this:
  PowerBuilder

  UPDATE Employee<br />SET Salary = 65000<br />WHERE Emp_ID = 1001 <i><br /> AND Salary = 50000</i><i> <br /> AND Emp_Fname = original_value</i><i> <br /> AND Emp_Lname = original_value</i><i> <br /> AND Status = original_value</i> <i><br /> ...</i>

  1	UPDATE Employee<br />SET Salary = 65000<br />WHERE Emp_ID = 1001    <i><br />   AND Salary = 50000</i><i>    <br />   AND Emp_Fname = original_value</i><i>    <br />   AND Emp_Lname = original_value</i><i>    <br />   AND Status = original_value</i>    <i><br />   ...</i>

  
  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.

Subscribe

Login

Notify of

new follow-up comments new replies to my comments

Label

{} [+]

Name*

Email*

Website

Label

{} [+]

Name*

Email*

Website

0 Comments

Oldest

Newest Most Voted

Inline Feedbacks

View all comments