Chapter 5
|
Supported standards 33
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, ...)
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.
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'). 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 following example inserts a list of expressions:
INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date)
VALUES ('Smith', 'John', 'E22345', 27500, {6/5/2004})
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 only text.
