ODBC and JDBC Guide

34 FileMaker ODBC and JDBC Guide
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'). Date, time, and timestamp values must be enclosed
in braces {}. Logical values that are characters must be enclosed in periods (for example, .T. or .F.).
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 only with text.
CREATE TABLE statement
Use the CREATE TABLE statement to create a table in a database file. The format of the CREATE TABLE
statement is:
CREATE TABLE table_name table_element_list [NOT NULL]
Within the statement, you specify the name and data type of each column.
table_name and table_element_list have a 100 character limit. Defining a column to be NOT NULL
automatically selects the Not Empty Validation Option for the corresponding field in the FileMaker database file.
The field is flagged as a Required Value in the Fields tab of the Manage Database dialog box in FileMaker
Pro.