Databases Reference
In-Depth Information
EXAMPLE 30
Delete any row in the OrderLine table in which the part number is BV06.
103
To delete data from the database, use the
DELETE
command, which consists of the word
DELETE
fol-
lowed 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-58. When you run this query in Access, a dia-
log 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-58
SQL query to delete rows
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-59a. 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.
MySQL does not support the query shown in Figure 3-59a. To accomplish the same task, you would cre-
ate the SmallCust table using a CREATE TABLE command. Then you would use an INSERT command to
insert the appropriate data into the SmallCust table, as shown in Figure 3-59b.
