Creating an UPDATE statement

An UPDATE statement is used to update data in a table.

In the UPDATE statement, you specify the database table to update, which columns to update, and provide the new data. The new data can be specified with values or with the results from executing an SQL statement.

Your update can be conditional. Using one or more conditions in a WHERE clause for the UPDATE statement, you can specify which rows in the database table to update. If you do not have a WHERE clause, all rows of the target table are updated.

The SET clause specifies which columns to update and provides new values for them. The table to update is specified prior to the SET clause.

To create an UPDATE statement in the SQL Builder:

  1. Switch to the Data Definition view in the Data perspective.

  2. Expand the project or folder that contains your database, and then expand the database nodes until you see the Statements folder.

  3. Right-click the Statements folder, and then click New > Update Statement on the pop-up menu.

  4. Type a name for the statement, and then click OK. The SQL Builder opens.

  5. To add the table in which to insert data, right-click in the Tables pane, and then click Add Table on the pop-up menu.

  6. In the Table name list, click the table to use, and then click OK.

After adding a table to the statement, you can create a SET clause.

To create a SET clause:

  1. In the Design pane, click the Set tab.

  2. Add columns individually or as a group to the SET clause. Do one of the following:

    • To add them individually, click a column in the list and then click the > button.

    • To add them as a group, press and hold down the Ctrl key as you click the columns to add, and then click the Group button

      Image of the group button. Within a group, values can be provided for individual columns or the entire group of columns. A subquery can be specified to provide values for a group of one or more columns.

  3. To provide values for one or more columns within a group, do the following:

    1. Select the row that contains the column or group of columns, click the cell under the Expression heading, and then do one of the following.

      • To specify a value, type an expression in the cell or click Build Expression in the list and use the Expression Builder wizard to build an expression.

      • To use a subquery to provide a set of values for a group, select an existing subquery from the cell list or create a new subquery. If you create a new subquery, you can complete it by selecting it in the Outline view.

    2. (Optional) Add a WHERE clause, as described below.

    3. To execute the SQL statement, click SQL > Execute. The output is shown in the DB Output view.

When you create a WHERE clause, you are specifying conditions that determine which row to update. Any row in the database that matches the condition(s) you specify is updated.

To create a WHERE clause:

  1. In the Design pane, click the Where tab.

  2. Under the Column heading, either click the column to include in your condition or click Build Expression and build the expression to include in your condition.

  3. Click the adjoining cell under the Operator heading, and then select an operator from the list.

  4. Click the adjoining cell under the Value heading, and then type a value. You can also select a column from the list or use the Expression Builder wizard to create a value by selecting Build Expression. The value can be a host variable that can be substituted with a value at execution time. Click outside of the row when you are finished.

  5. Add further conditions if needed. Before adding another condition, select from adjoining cell under the And/Or heading to specify how to evaluate the conditions. For example, if two conditions must both be true for a row to be updated, click AND in the list for the first condition.

  6. When you have finished, click SQL > Execute to execute the SQL statement. The output is shown in the DB Output view.

To create an UPDATE statement using the Create A New SQL Statement wizard:

  1. Click File > New > Other.

  2. In the New window, expand the Data folder, click SQL Statement, and then click Next.

  3. In the SQL statement field of the wizard, click UPDATE.

  4. Fill in the remaining fields on the wizard pages, as necessary. To see information about a particular field, position the pointer in the field and then press F1.

UPDATE statement
An UPDATE statement is used to update data in a table. In the UPDATE statement, specify the database table to be updated, which columns should be updated, and provide the new data. The new data can be specified with values or with the results from a query.

 

Parent topic

SQL statement using either the SQL Builder or the Create A New SQL Statement wizard. Both tools support the creation of SELECT, UPDATE, INSERT and DELETE statements. The SQL Builder also supports WITH (DB2 only) and FULLSELECT statements. Any statement that you create is stored in a file with the extension .sqx.">Creating an SQL statement

 

Related tasks

SQL statements.">Editing an SQL statement
SQL statement that you are creating.">Executing an SQL statement