ALLBASE/SQL Reference Manual (36216-90216)

82 Chapter2

Using ALLBASE/SQL

Manipulating Data

You can copy rows from one or more tables or views into another table by using a form of

the INSERT statement (often called a type 2 Insert) in which you specify the following

items:

1. A table or view name

2. A SELECT statement

Note that the numbers in the next example refer to the items listed above:

INSERT INTO PurchDB.Drives

SELECT * FROM PurchDB.Parts -- 2

WHERE PartName LIKE 'Drives%'

The rows in the query result produced by the SELECT statement are inserted into

PurchDB.Drives. The SELECT statement cannot contain an ORDER BY clause and cannot

name the target table in the FROM clause. The target table must exist prior to an INSERT

operation.

Updating Data

You change data in one of more columns by using the UPDATE statement. These are the

components of the UPDATE statement:

1. The name of a table or a view

2. A SET clause

3. A WHERE clause

The following example illustrates the UPDATE statement and its components; the reference

numbers identify the components listed above.

UPDATE PurchDB.Parts --1

SET SalesPrice = 15.95 --2

WHERE PartNumber = '9999-AJ' --3

Only a single table name or view name can be speciﬁed. Only certain views can be used to

update, as described under “Updatability of Queries” in Chapter 3 , “SQL Queries.” For

each column to be updated, you specify a column name and value in the SET clause. NULLis

a valid value for columns that can contain null values. Unless you specify a WHERE clause,

all

rows of the named table or view are updated. A search condition in this clause

describes which rows to update. The search condition in the previous example speciﬁes

that the row(s) to be updated must name PartNumber 9999-AJ.

Deleting Data

You use the DELETE statement to delete entire rows. This statement has two components

as follows:

1. A table or view name

2. A WHERE clause