ODBC and JDBC Guide

42 FileMaker ODBC and JDBC Guide
DELETE statement
Use the DELETE statement to delete records from a database table. The format of the DELETE statement is:
DELETE FROM table_name [ WHERE { conditions } ]
Note The WHERE clause determines which records are to be deleted. If you don’t include the WHERE
keyword, all records in the table are deleted (but the table is left intact).
An example of a DELETE statement on the Employee table is:
DELETE FROM emp WHERE emp_id = 'E10001'
Each DELETE statement removes every record that meets the conditions in the WHERE clause. In this case,
every record having the employee ID E10001 is deleted. Because employee IDs are unique in the Employee
table, only one record is deleted.
INSERT statement
Use the INSERT statement to create records in a database table. You can specify either:
1 A list of values to be inserted as a new record
1 A SELECT statement that copies data from another table to be inserted as a set of new records
The format of the INSERT statement is:
INSERT INTO table_name [(column_name, ...)] VALUES (expr, ...)
[, VALUES (expr, ...)]
column_name is an optional list of column names that provides the name and order of the columns whose
values are specified in the VALUES clause. If you omit
column_name, the value expressions (expr) must
provide values for all columns defined in the table and must be in the same order that the columns are defined
for the table.
column_name may also specify a field repetition, for example lastDates[4].
expr is the list of expressions giving the values for the columns of the new record. 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 following example inserts a list of expressions:
INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date)
VALUES ('Smith', 'John', 'E22345', 27500, {d ‘2008/06/05’})
Each INSERT statement adds one record to the database table. In this case a record has been added to the
employee database table, EMP. Values are specified for five columns. The remaining columns in the table
are assigned a blank value, meaning Null.
Note In container fields, you can INSERT 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.
The SELECT statement is a query that returns values for each column_name value specified in the column
name list. Using a SELECT statement instead of a list of value expressions lets you select a set of rows from
one table and insert it into another table using a single INSERT statement.