Database Guide
You can create UPDATE statements to update existing data in a
database.
In this example, you will use the SQL Statement part and SQL Editor to
create the following UPDATE statement:
UPDATE STAFF
SET SALARY = (SALARY + (SALARY * .10))
WHERE (JOB = 'Clerk')
- Create a new visual part and select
, the SQL Statement part, from the Database Functions category.
- Add the SQL Statement part to the free-form surface and open its settings
view. It appears as shown in the following illustration. You use
this window to define or choose an SQL statement to be executed.

- In the Connection alias field, select a connection alias from
the drop-down list.
You can check the status of this connection specification by selecting the
Specs push button. This button displays the Database
connection specifications window. If the value in the
Active? field is Yes, then you are connected to the
database manager. If the value in this field is No, then you
can connect to the database manager by selecting the connection specification,
pressing mouse button 2 to display the pop-up menu, and then selecting
Connect. If you are not already logged on to the DBMS,
VisualAge prompts you to log on.
- In the Access set field, select an access set from the
drop-down list.
- Select Create > UPDATE from the
Query menu.
The UPDATE Details window is displayed and looks like this:

- Select the STAFF table from the Table/view drop-down
list. The columns belonging to that table are displayed in the
Columns list. Because you can only work with one table at a
time, these columns are unqualified.
Setting column values
Select the SALARY column in the Columns list.
When you select a column, it is displayed in the SET columns
list and given a default value displayed in the Column value text
field. The default value is a host variable with the same name as the
column and can be changed by typing in the text field or by using the
Column value menu. The type of data the column can hold is
also displayed for you under the Column value text field.
The Columns list also has pop-up menu for selecting or deselecting all of
the columns at once.
Specifying an expression
This sample statement requires you to change the default column value to a
compound expression. This means you need to create two
expressions: (SALARY * .10) and (SALARY + (SALARY
* .10)).
- To create these expressions, select Specify expression from the
Column value menu. The Expression Details window is
displayed.
- To create the expression (SALARY * .10), select SALARY
in the Left operand list and * in the
Operator list. Type .10 in the Right
operand text field.
- Now select Add to operand lists. This adds the partial
expression to the Left and Right operand lists so that
it can then be used as an operand in a compound expression.
- To finish the expression, select SALARY in the Left
operand list and + in the Operator list, then
select (SALARY * .10) in the Right operand text
field.
- Select Apply to save your expression and return to the UPDATE
Details window.
Now you are ready to specify the WHERE clause.
- Select the WHERE clause push button to display the WHERE
Details window.
- Select JOB in the Left operand list, = in
the Operator list, and type 'Clerk' in the
Right operand list.
- Select Apply to save your WHERE clause and return to the UPDATE
Details window. If you want, you can name and save your UPDATE
statement.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]