Defining an UPDATE statement
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')
SQL Sttement Part
1. Create a new visual part and select 
, the SQL Statement part, from the Database Functions category.
, the SQL Statement part, from the Database Functions category. 2. 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.
3. 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, VA Smalltalk prompts you to log on.
4. In the Access set field, select an access set from the drop-down list.
5. Select Create > UPDATE from the Query menu.
The UPDATE Details window is displayed and looks like this:
6. 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)).
1. To create these expressions, select Specify expression from the Column value menu. The Expression Details window is displayed.
2. 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.
3. 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.
4. 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.
5. Select Apply to save your expression and return to the UPDATE Details window.
Now you are ready to specify the WHERE clause.
6. Select the WHERE clause push button to display the WHERE Details window.
7. Select JOB in the Left operand list, = in the Operator list, and type 'Clerk' in the Right operand list.
8. 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.
Last modified date: 06/01/2018