Database Reference
In-Depth Information
EXAMPLE 30
Delete any row in the OrderLine table in which the part number is BV06.
105
To delete data from the database, use the DELETE command, which consists of the word DELETE
followed by a FROM clause identifying the table. Use a WHERE clause to specify a condition to select the
records to delete. If you omit the condition for selecting the records to delete, when you run the query, it will
delete all records from the table.
The DELETE command for this example is shown in Figure 3-59. When you run this query in Access, a
dialog box opens and indicates the number of records the DELETE command will delete. In this case, you
would delete only one record because the WHERE clause selects part number BV06.
Table from which
to delete rows
Condition to select
part number BV06
FIGURE 3-59
SQL query to delete rows
CREATING A TABLE FROM A QUERY
You can save the results of a query as a table by including the INTO clause in the query, as illustrated in
Example 31.
EXAMPLE 31
Create a new table named SmallCust consisting of all fields from the Customer table and those rows in which
the credit limit is less than or equal to $7,500.
To create the SmallCust table, create a query to select all fields from the Customer table, include a
WHERE clause to restrict the rows to those in which CreditLimit
7500, and include an INTO clause. The
INTO clause precedes the FROM clause and consists of the word INTO followed by the name of the table to
be created. The query appears in Figure 3-60a. When you run this query in Access, a dialog box opens and
indicates the number of records the INTO clause will paste into the new table. In this case, you would add six
rows to the SmallCust table.
Oracle and MySQL do not support the query shown in Figure 3-60a. To accomplish the same task, you
would create the SmallCust table using a CREATE TABLE command. You would then use an INSERT com-
mand to insert the appropriate data into the SmallCust table. Figure 3-60b shows the query in MySQL.
<ΒΌ
