ODBC and JDBC Guide

Chapter 7
|
Supported standards 43
Here's an example of an INSERT statement that uses a SELECT statement:
INSERT INTO emp1 (first_name, last_name, emp_id, dept, salary)
SELECT first_name, last_name, emp_id, dept, salary from emp
WHERE dept = ‘D050’
In this type of INSERT statement, the number of columns to be inserted must match the number of columns
in the SELECT statement. The list of columns to be inserted must correspond to the columns in the SELECT
statement just as it would to a list of value expressions in the other type of INSERT statement. For example,
the first column inserted corresponds to the first column selected; the second inserted to the second, and so on.
The size and data type of these corresponding columns must be compatible. Each column in the SELECT
list should have a data type that the ODBC or JDBC client driver accepts on a regular INSERT/UPDATE of
the corresponding column in the INSERT list. Values are truncated when the size of the value in the
SELECT list column is greater than the size of the corresponding INSERT list column.
The SELECT statement is evaluated before any values are inserted.
UPDATE statement
Use the UPDATE statement to change records in a database table. The format of the UPDATE statement is:
UPDATE table_name SET column_name = expr, ... [ WHERE { conditions } ]
column_name is the name of a column whose value is to be changed. Several columns can be changed in
one statement.
expr is the new value for the column.
Usually the expressions are constant values for the columns (but they can also be a subquery). You must
enclose character string values in pairs of single quotation marks ('). To include a single quotation mark in
a character string value enclosed by single quotation marks, use two single quotation marks together (for
example, 'Don''t').
Subqueries must be enclosed in parentheses.
The WHERE clause is any valid clause. It determines which records are updated.
An example of an UPDATE statement on the Employee table is:
UPDATE emp SET salary=32000, exempt=1 WHERE emp_id = 'E10001'
The UPDATE statement changes every record that meets the conditions in the WHERE clause. In this case
the salary and exempt status are changed for all employees having the employee ID E10001. Because
employee IDs are unique in the Employee table, only one record is updated.
Here's an example using a subquery:
UPDATE emp SET salary = (SELECT avg(salary) from emp) WHERE emp_id = 'E10001'
In this case, the salary is changed to the average salary in the company for the employee having employee
ID E10001.
Note In container fields, you can UPDATE with text only, unless you prepare a parameterized statement
and stream the data from your application. To use binary data, you must specify the type in a PutAs()
function: PutAs(col, ‘type’), where the type value is a type as described in
“Retrieving the contents
of a container field: CAST() function and GetAs() function” on page 40.