ODBC and JDBC Guide

Chapter 7 | Supported standards 41
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).
Example
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 39.