Manipulating Data

• Selecting data

• Inserting data into tables

• Updating rows in tables

• Deleting rows

Inserting Data

You use the INSERT statement to add rows to a table, specifying the following information:

The following example contains numbers that refer to the items in the list above:

                            1
                            |
   INSERT INTO PurchDB.Parts
               (PartNumber, PartName)              --2
        VALUES ('9999-AJ','Interface Engine')
               |                            |
               ----------------------------
                            |
                            3

Only a single table name or view name can be specified. Only certain views can be used to insert rows into a base table, as described under "Updatability of Queries" in Chapter 3 “SQL Queries”

The column names can be omitted if you are going to put a value into every column in the row. Otherwise, you name the columns you want to assign values to, enclosing the column names in parentheses and separating multiple column names with commas. Columns not named are assigned their default values. If no default exists for a column, it is assigned the null value. If you define a column as NOT NULL when you create a table, then you must assign a non-null value or specify a default value to the column.

The column values are also enclosed in parentheses and separated by commas. Character data is delimited with single quotation marks. The value NULL can be entered into columns that permit null values.

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:

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

                      1
                      |
   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:

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 specified. 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. NULL is 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 specifies 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:

The following example illustrates the DELETE statement and its two components:

           DELETE FROM PurchDB.Parts            --1  
              WHERE PartNumber = '9999-AJ'      --2  

Only a single table name or view name can be specified. Only certain views can be used to delete rows, as described under "Updatability of Queries" in Chapter 3 “SQL Queries”

The WHERE clause is optional. You omit it if you want to delete all the rows in a table or view. Otherwise, you use it to specify a search condition for which row(s) to delete.