|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 15
  - Updating Databases with SQL
    - Data Manipulation SQL Keywords
      - Adding Rows with the INSERT Statement
    - Listing 15.1. Testing the INSERT INTO keyword.
      - Figure 15.1.
    - Listing 15.2. Handling COUNTER and blank fields in INSERT statements.
      - Figure 15.2.
    - Creating Append Queries with INSERT INTO_FROM
    - Listing 15.3. Using the INSERT INTO_FROM statement.
      - Figure 15.3.
    - Listing 15.4. Adding the IN clause.
      - Figure 15.4.
      - Figure 15.5.
      - Figure 15.6.
    - Creating UPDATE Queries with the UPDATE_SET Statement
    - Listing 15.5. Sample code for record-oriented updates.
    - Listing 15.6. Using the UPDATE_SET statement.
      - Figure 15.7.
    - Listing 15.7. Adding the WHERE clause to the UPDATE statement.
      - Figure 15.8.
    - Creating Make Table Queries Using the SELECT_INTO_FROM Statement
    - Listing 15.8. Testing Make Table queries.
      - Figure 15.9.
    - Listing 15.9. Using the WHERE clause to limit Make Table queries.
      - Figure 15.10.
    - Listing 15.10. Using GROUP BY and HAVING to summarize data.
      - Figure 15.11.
    - Creating Delete Table Queries Using DELETE_FROM
    - Listing 15.11. Using the DELETE statement.
      - Figure 15.12.
    - Listing 15.12. Using a complex WHERE clause with a DELETE statement.
      - Figure 15.13.
    - Listing 15.13. Using JOIN to perform a multitable DELETE.
      - Figure 15.14.
      - Figure 15.15.
    - Summary
    - Quiz
    - Exercises

## Day 15

## Updating Databases with SQL

In today's lesson, you learn about the SQL Data Manipulation Language (DML) keywords you can use to update and modify data in existing tables. Although most of the time you use Visual Basic data entry forms and Visual Basic program code to perform data table updates, there are often times when it is more desirable to use SQL statements to update your data tables.

When you complete the examples in this chapter, you will be able to:

- Alter the contents of existing tables using the `UPDATE` statement.

- Add new rows to existing tables with the `INSERT INTO` statement.

- Append rows from one table to another using the `INSERT INTO_FROM` clause.

- Copy one or more rows from an existing table to a new table using the `SELECT_INTO` keywords.

- Remove selected rows from a table using the `DELETE_FROM` clause.

> NOTE: Throughout this chapter, you use the `SQL-VB5` program to create and run SQL scripts. The lesson on Day 13, "Creating Databases with SQL," contains a short tutorial on where to locate the `SQL-VB5` program and how to use it. If you have not worked through the lesson on Day 13 yet, now is a good time to review at least the first half of that chapter.

### Data Manipulation SQL Keywords

The Data Manipulation Language (DML) SQL keywords are used to add new data to existing tables, edit existing table data, append data from one table to another, copy data from one table to an entirely new table, and delete data rows from existing tables.

Most of the time, your Visual Basic programs use data-entry screens to perform these tasks. However, sometimes the DML keywords come in handy. In some back-end database systems, these SQL keywords are the only way you can add, edit, or delete data from tables. At other times, these SQL keywords give you the power to produce updates to large tables with very few lines of code and in a relatively short amount of time.

Also, many times you might need to select a small subset of data from your tables for a report or a graphic display. Instead of creating Dynaset views of existing tables, you might want to create a frozen Snapshot of the data to use for this purpose. What you need to do is copy some records from an existing table into a new table for use in reporting and displays. SQL DML keywords can help create these select tables quickly without extensive Visual Basic code.

Another example of using SQL DML keywords is when you want to append a set of records from one table to another. Instead of writing Visual Basic code routines that read a record from one table and then write it to another, you can use SQL DML keywords to perform the table update--many times with just one line of SQL code.

Finally, SQL DML keywords allow you to quickly delete entire tables or subsets of data in a single SQL statement. This reduces the amount of Visual Basic code you need to write and also greatly speeds the processing in most cases.

#### Adding Rows with the INSERT Statement

The `INSERT` statement is used to insert values into data tables. You can use the `INSERT` statement to populate data tables automatically--without the need for data-entry screens. Also, you can perform this automatic data entry using very little Visual Basic code. Why Use INSERT Statements? Even though you most often perform data entry using Visual Basic-coded data-entry screens tied to Visual Basic data controls, there are times when using the `INSERT` statement can prove more efficient. An excellent example of using `INSERT` statements is the installation of a new database system. Often, several data tables need to be populated with default values before people can start using a system. You can use the `INSERT` statement to perform the initial data load.

Another use for the `INSERT` statement is in converting data from one database to another. Often, you can use `INSERT` statements to load existing data in one format into your newly designed relational database.

Finally, you can use `INSERT` statements to quickly add data to tables that would be too tedious to enter using data-entry screens. Using the INSERT INTO Statement The basic form of the `INSERT` statement is

    INSERT INTO TableName(field1, field2) VALUES (value1, value2);

> `NOTE:``INSERT` and `INSERT INTO` statements are often used interchangeably. For the most part, this book uses the latter term.

The `INSERT` SQL statement has three parts. The TableName identifies the table that you want to update. The `(`field1`,` field2`)` part of the statement identifies the columns into which you add data. The `(`value1`,` value2`)` part of the statement identifies the exact values you add to the fields you identified. You can name as few or as many fields as you like in the field portion of the statement. However, you must supply a list of values that has the same number of values and the same data type as those identified in the field portion of the statement. Also, you must list the values in the same order as the fields. The first value is placed in the first field, the second value in the second field, and so on.

Let's use `SQL-VB5` to create a working example of the `INSERT` statement. Open a new `.SQV` script called `SQLVB05.SQV` using the File | New command from the main menu. Enter the script shown in Listing 15.1, save it, and execute it using the File | Run menu option. Refer to Figure 15.1 to compare your results.

### Listing 15.1. Testing the INSERT INTO keyword.

    //
    // sqlvb05.sqv - Testing the INSERT INTO keyword
    //
    // create a new database
    dbMake sqlvb05.mdb;
    // create a new table
    CREATE TABLE JobTitles
       (JobID TEXT(5) CONSTRAINT PKJobTitle PRIMARY KEY,
        JobName TEXT(20),
        JobDesc MEMO
       );
    // insert some data
    INSERT INTO JobTitles(JobID, JobName, JobDesc) VALUES
       (`J001',
        `President',
        `Presides over the company'
       );
    INSERT INTO JobTitles(JobID, JobName, JobDesc) VALUES
       (`J002',
        `Vice President',
        `Does what the President tells him to do'
       );
    // display results
    SELECT * FROM JobTitles;
    // eof

Figure 15.1. The results of the `INSERT INTO` statement.
Notice that you must use a separate `INSERT INTO` statement for each row you want to add to the table. If you wanted to add 10 more job descriptions to the JobTitles table, you would need to add 10 more `INSERT INTO` statements to the script.

Also, because you defined the JobsTitles.JobID column as the primary key, you are required to fill that field with unique, non-null data each time you execute the `INSERT INTO` statement. If you provide a null value or leave the JobsTitles.JobID field out of the `INSERT INTO` statement, you get a database error message.

If you use a COUNTER data type field in your table, you can't include that in the field list of the `INSERT INTO` statement. Visual Basic and the SQL engine fill the COUNTER field with an appropriate value. Also, you do not have to add data to every column in the row. If there are fields in the data table that are not required and that can be left null, you can simply omit them from the `INSERT INTO` statement. The code example in Listing 15.2 illustrates these last two points. Use `SQL-VB5` to edit the `SQLVB05.SQV` script to match the one in Listing 15.2. Save and execute the script. Check your results against those in Figure 15.2.

### Listing 15.2. Handling COUNTER and blank fields in INSERT statements.

    //
    // sqlvb05.sqv - Testing the INSERT INTO keyword
    //
    // create a new database
    dbMake sqlvb05.mdb;
    // create a new table
    CREATE TABLE JobTitles
       (JobCounter COUNTER,
        JobID TEXT(5) CONSTRAINT PKJobTitle PRIMARY KEY,
        JobName TEXT(20),
        JobPay CURRENCY,
        JobDesc MEMO
       );
    // insert some data
    INSERT INTO JobTitles (JobID, JobName, JobDesc, JobPay) VALUES
       (`J001',
        `President',
        `Presides over the company',
        `50000'
       );
    INSERT INTO JobTitles (JobID, JobName, JobDesc, JobPay) VALUES
       (`J002',
        `Vice President',
        `Does what the President tells him to do',
        `40000'
       );
    INSERT INTO JobTitles (JobID, JobPay, JobName) VALUES
       (`J003',
        `35000',
        `Chief Engineer'
       );
    // display results
    SELECT * FROM JobTitles;
    // eof

Notice that the JobTitles.JobCounter column was automatically populated by Visual Basic. Also, you can see that the JobTitles.JobDesc column was left blank for the third record in the table.
Figure 15.2. The results of using `INSERT INTO` with COUNTER and optional fields.
Two other interesting things about the `INSERT INTO` statement are illustrated in the code example in Listing 15.2. Notice that the values for the JobTitles.JobPay column were surrounded by quotation marks even though the data type is CURRENCY. When you use the `INSERT INTO` statement, all values must be surrounded by quotation marks. SQL and Visual Basic handle any type conversions needed to insert the values into the identified fields.

The second interesting thing to note in Listing 15.2 is the order in which columns are listed in the `INSERT INTO` statements. If you look at each of the statements, you see that the JobTitles.JobPay column appears in different places within the field list. When you use the `INSERT INTO` statement, you can list the columns in any order. You only need to make sure that you list the values to be inserted in the same order in which you list the columns.

You have learned how to use the `INSERT INTO` statement to add individual rows to a table. This is commonly called a single-record insert. In the next section, you learn about a more powerful version of the `INSERT INTO` statement, commonly called an append query.

#### Creating Append Queries with INSERT INTO_FROM

The `INSERT INTO_FROM` version of the `INSERT` statement allows you to insert multiple records from one table into another table. This multirecord version of `INSERT INTO` is called an append query because it enables you to append rows from one table to the end of another table. As long as the two tables you are working with have fields with the same name, you can use the `INSERT INTO_FROM` statement.

The basic format of the `INSERT INTO_FROM` statement is

    INSERT INTO TargetTable SELECT field1, field2 FROM SourceTable;

There are three important parts of the `INSERT INTO_FROM` statement. The first part is the TargetTable. This is the table that is updated by the statement. The second part is `SELECT field`s. This is a list of the fields that are updated in the TargetTable. These are also the fields that are supplied by the third part of the statement--the SourceTable. As you can see, the `INSERT INTO_FROM` statement is really just a `SELECT_FROM` query with an `INSERT INTO` TargetTable in front of it.

Now, let's update the `SQLVB05.SQV` to provide an example of the `INSERT INTO_FROM` statement. First, use `SQL-VB5` to load and edit the `SQLVB05.SQV` script. Make changes to the script so that it matches Listing 15.3. Save the script and run it. Check your results against those shown in Figure 15.3.

### Listing 15.3. Using the INSERT INTO_FROM statement.

    //
    // sqlvb05.sqv - Testing the INSERT INTO keyword
    //
    // create a new database
    dbMake sqlvb05.mdb;
    // create a new table
    CREATE TABLE JobTitles
       (JobCounter COUNTER,
        JobID TEXT(5) CONSTRAINT PKJobTitle PRIMARY KEY,
        JobName TEXT(20),
        JobPay CURRENCY,
        JobDesc MEMO
       );
    // insert some data
    INSERT INTO JobTitles (JobID, JobName, JobDesc, JobPay) VALUES
       (`J001',
        `President',
        `Presides over the company',
        `50000'
       );
    INSERT INTO JobTitles (JobID, JobName, JobDesc, JobPay) VALUES
       (`J002',
        `Vice President',
        `Does what the President tells him to do',
        `40000'
       );
    INSERT INTO JobTitles (JobID, JobPay, JobName) VALUES
       (`J003',
        `35000',
        `Chief Engineer'
       );
    // create a second table to hold some of the info from JobTitles
    CREATE TABLE JobReport
       (JobID TEXT(5) CONSTRAINT PKJobReport PRIMARY KEY,
        JobName TEXT(20),
        JobDesc MEMO,
         DeptID  TEXT(5)
       );
    // now append records from JobTitles into JobReport
    INSERT INTO JobReport
       SELECT JobID, JobName, JobDesc FROM JobTitles;

    // display results
    SELECT * FROM JobTitles;
    SELECT * FROM JobReport;
    // eof

Figure 15.3. The results of the `INSERT INTO_FROM` statement.

> NOTE: You might have noticed in Listing 15.3 that you created two indexes, each on an identical column name, but you gave the two indexes different names. SQL does not allow you to use the same name on different indexes, even if they refer to different tables. Indexes appear as independent data objects in a Microsoft Access database. Each object must have a unique name.

Notice that the `INSERT INTO_FROM` statement lists only those fields that are present in both tables. You need to list the columns by name in this example because the JobReport table does not contain all the fields that the JobTitles table contains. If both tables were an exact match, you could use the asterisk wildcard (`*`) character in the `SELECT` clause. For example, if JobTitles and JobReport shared all the same column names, you could use the following SQL statement to append data from one to the other:

    INSERT INTO JobReport SELECT * FROM JobTitles;

You can also use the `INSERT INTO` statement to append rows to tables in another database. You accomplish this by adding an `IN` clause to the first part of the statement. For example, you can add rows from the JobTitles table in `SQLVB05.MDB` to a similar table in another database called `SQLVB05B.MDB`. The syntax for the `IN` clause of an `INSERT INTO_FROM` statement is

    IN "DatabaseFileName" "DatabaseFormat"

DatabaseFileName is the complete database filename including the drive identifier and the path name of the destination (or external) database. DatabaseFormat is the name of the database format of the destination database, such as FoxPro, dBASE, Paradox, and so on. For example, if you want to update TableOne in the external database called `EXTERNAL.MDB` on drive `C` in the directory called `DB`, you would use the following `IN` clause for the `SELECT INTO` statement:

    SELECT INTO TableOne IN "c:\db\external.mdb" "access"

Listing 15.4 shows how this is done using a real set of database files. Use `SQL-VB5` to load and edit `SQLVB05.SQV` to match the modifications outlined in Listing 15.4. Save the script and execute it. Your results should look similar to those in Figure 15.4.

### Listing 15.4. Adding the IN clause.

    //
    // sqlvb05.sqv - Testing the INSERT INTO keyword
    //
    // create sqlvgb05b database
    dbMake sqlvb05b.mdb;
    // make a table
    CREATE TABLE OtherTitles
       (JobCounter COUNTER,
        JobID TEXT(5) CONSTRAINT PKJobTitle PRIMARY KEY,
        JobName TEXT(20),
        JobPay CURRENCY,
        JobDesc MEMO
       );
    // insert some rows
    INSERT INTO OtherTitles (JobID, JobName, JobDesc, JobPay) VALUES
       (`J004',
        `Line Foreman',
        `Supervises production line',
        `30000'
       );
    INSERT INTO OtherTitles (JobID, JobName, JobDesc, JobPay) VALUES
       (`J005',
        `Line Worker',
        `Does what the Line Foreman tells him to do',
        `25000'
       );
    // show results
    SELECT * FROM OtherTitles;
    // now close this database
    dbClose;
    // ********************************************************
    // create a new database
    dbMake sqlvb05.mdb;
    // create a new table
    CREATE TABLE JobTitles
       (JobCounter COUNTER,
        JobID TEXT(5) CONSTRAINT PKJobTitle PRIMARY KEY,
        JobName TEXT(20),
        JobPay CURRENCY,
        JobDesc MEMO
       );
    // insert some data
    INSERT INTO JobTitles (JobID, JobName, JobDesc, JobPay) VALUES
       (`J001',
        `President',
        `Presides over the company',
        `50000'
       );
    INSERT INTO JobTitles (JobID, JobName, JobDesc, JobPay) VALUES
       (`J002',
        `Vice President',
        `Does what the President tells him to do',
        `40000'
       );
    INSERT INTO JobTitles (JobID, JobPay, JobName) VALUES
       (`J003',
        `35000',
        `Chief Engineer'
       );
    // create a second table to hold some of the info from JobTitles
    CREATE TABLE JobReport
       (JobID TEXT(5) CONSTRAINT PKJobReport PRIMARY KEY,
        JobName TEXT(20),
        JobDesc MEMO
       );
    // now append records from JobTitles into JobReport
    INSERT INTO JobReport
       SELECT JobID, JobName, JobDesc FROM JobTitles;

    // display results
    SELECT * FROM JobTitles;
    SELECT * FROM JobReport;
    // now append data from one database to another
    INSERT INTO OtherTitles IN "sqlvb05b.mdb" "Access"
       SELECT JobID, JobName, JobDesc, JobPay FROM JobTitles;
    // close this db
    dbClose;
    // open other db
    dbOpen sqlvb05b.mdb
    // show updated table
    SELECT * FROM OtherTitles;
    // eof

Figure 15.4. The results of the `INSERT INTO_FROM` statement with the `IN` clause.
The script in Listing 15.4 first creates a database with a single table (OtherTitles) that has two records in the table. Then the script displays the table for a moment before the database is closed. Notice that the records in the table have OtherTitles.JobCounter values of `1` and `2`. Then the script creates the JobTitles table in another database and populates that table with three records. Other tables are populated (this was done in previous examples), and eventually the JobTitles table is displayed. The three records have JobTitles.JobCounter values of `1`, `2`, and `3`. Finally, the `INSERT INTO_FROM_IN` statement is executed to update the external data table. Then the external table is opened so that you can view the results.

Now look at the OtherTitles.JobCounter values. What has happened? When you append COUNTER data fields to another table, the new records are renumbered. This ensures unique counter values in the table. If you want to retain the old numbers, you can include the COUNTER field in your `INSERT INTO` list. To illustrate this, add the JobCounter column name to the field list in the `INSERT INTO` statement that updated the external table (see Figure 15.5). Now execute the script again to see the results.
Figure 15.5. The results of the `INSERT INTO_FROM_IN` with an updated counter column.
As you can see in Figure 15.5, you now have duplicate COUNTER values in your table. This can lead to data integrity problems if you are using the COUNTER data type as a guaranteed unique value. You should be careful when you use `INSERT INTO` statements that contain COUNTER data type columns.

> WARNING: The Microsoft Visual Basic documentation for the behavior of `INSERT INTO` with COUNTER data types states that duplicate counter values are not appended to the destination table. This is not correct. The only time duplicates are not included in the destination tables is when the COUNTER data type column is defined as the primary key.

We should point out here that if you attempt to append records to a table that has a duplicate primary key value, the new record is not appended to the table--and you do not receive an error message! If you edit the `SQLVB05.SQV` script to renumber the OtherTitles.JobID values to `J001` and `J002`, you see a different set of results when you run the script. Figure 15.6 shows what you get when you attempt to update duplicate primary key rows.
Figure 15.6. The results of attempting to append duplicate primary key rows.
The fact that SQL does not append records with a duplicate key can be used as an advantage. You can easily merge two tables that contain overlapping data and get a single result set that does not contain duplicates. Anyone who has worked with mailing lists can find a use for this feature of the `INSERT INTO` statement.

Now that you know how to insert rows into tables, it's time to learn how you can update existing rows using the `UPDATE_SET` statement.

#### Creating UPDATE Queries with the UPDATE_SET Statement

The `UPDATE_SET` statement enables you to update a large amount of data in one or more tables very quickly with very little coding. You use the `UPDATE_SET` statement to modify data already on file in a data table. The advantage of the `UPDATE_SET` statement is that you can use a single statement to modify multiple rows in the table.

For example, assume that you have a table of 500 employees. You are told by the Human Resources department that all employees are to be given a 17.5 percent increase in their pay starting immediately (wouldn't it be nice?). You could write a Visual Basic program that opens the table, reads each record, computes the new salary, stores the updated record, and then goes back to read the next record. Your code would look something like the pseudocode sample in Listing 15.5.

> NOTE: Listing 15.5 is not a real Visual Basic program; it is just a set of statements that read like program code. Such pseudocode is often used by programmers to plan out programs without having to deal with the details of a particular programming language. Another benefit of using pseudocode to plan programs is that people do not need to know a particular programming language to be able to understand the example.

### Listing 15.5. Sample code for record-oriented updates.

    OPEN EmpDatabase
    OPEN EmpTable
    DO UNTIL END-OF-FILE (EmpTable)
       READ EmpTable RECORD
       EmpTable.EmpSalary = EmpTable.EmpSalary * 1.175
       WRITE EmpTable RECORD
    END DO
    CLOSE EmpTable
    CLOSE EmpDatabase

This is a relatively simple process, but--depending on the size of the data table and the speed of your workstation or the database server--this kind of table update could take quite a bit of time. You can use the SQL `UPDATE` statement to perform the same task.

    OPEN database
    UPDATE EmpTable SET EmpSalary = EmpSalary * 1.175
    CLOSE database

The preceding example shows how you can accomplish the same task with less coding. Even better, this code runs much faster than the walk-through loop shown in Listing 15.5, and this single line of code works for any number of records in the set. Furthermore, if this statement is sent to a back-end database server connected by ODBC and not processed by the local workstation, you could see an even greater increase in processing speed for your program.

Let's start a new program that illustrates the `UPDATE_SET` statement. Use `SQL-VB5` to create a new script file called `SQLVB06.SQV` and enter the commands in Listing 15.6. After you save the script, execute it and check your results against those in Figure 15.7.

### Listing 15.6. Using the UPDATE_SET statement.

    //
    // sqlvb06.sqv - testing the UPDATE ... SET statement
    //
    // create a database
    dbMake sqlvb06.mdb;
    // create a table
    CREATE TABLE EmpTable
       (EmpID TEXT(5) CONSTRAINT PKEmpTable PRIMARY KEY,
        EmpName TEXT(30),
        EmpSalary CURRENCY
       );
    // insert some data
    INSERT INTO EmpTable VALUES
       (`E001',
        `Anderson, Shannon',
        `35000'
       );
    INSERT INTO EmpTable VALUES
       (`E002',
        `Billings, Jesse',
        `30000'
       );
    INSERT INTO EmpTable VALUES
       (`E003',
        `Caldwell, Dana',
        `25000'
       );
    // show first result set
    SELECT * FROM EmpTable AS FirstPass;
    // now perform update
    UPDATE empTable SET EmpSalary = EmpSalary * 1.175;
    // show new results
    SELECT * FROM EmpTable AS SecondPass;
    // eof

Figure 15.7. The results of using the `UPDATE_SET` statement.

> NOTE: Notice that you did not include the column names in the `INSERT INTO` statements in this example. As long as you are supplying all the column values for a table, in the same order that they appear in the physical layout, you can omit the column names from the statement.

As you can see in Figure 15.7, all the records in the table are updated by the `UPDATE_SET` statement. The `SET` statement works for both numeric and character fields. It can contain any number of column updates, too. For example, if you have a table that has three fields that need to be updated, you can use the following SQL statement:

    UPDATE MyTable SET
       CustType="RETAIL",
       CustDiscount=10,
       CustDate=#01/15/96#;

You can also add a `WHERE` clause to the `UPDATE` statement to limit the rows that are affected by the `SET` portion of the statement. What if you want to give anyone whose salary is over $30,000 a 10 percent raise and anyone whose salary is $30,000 or under a 15 percent raise? You could accomplish this with two `UPDATE_SET` statements that each contain a `WHERE` clause. Use the code in Listing 15.7 as a guide to modifying the `SQLVB06.SQV` script. Save your changes and run the script. Check your results against Figure 15.8.

### Listing 15.7. Adding the WHERE clause to the UPDATE statement.

    //
    // sqlvb06.sqv - testing the UPDATE ... SET statement
    //
    // create a database
    dbMake sqlvb06.mdb;
    // create a table
    CREATE TABLE EmpTable
       (EmpID TEXT(5) CONSTRAINT PKEmpTable PRIMARY KEY,
        EmpName TEXT(30),
        EmpSalary CURRENCY
       );
    // insert some data
    INSERT INTO EmpTable VALUES
       (`E001',
        `Anderson, Shannon',
        `35000'
       );
    INSERT INTO EmpTable VALUES
       (`E002',
        `Billings, Jesse',
        `30000'
       );
    INSERT INTO EmpTable VALUES
       (`E003',
        `Caldwell, Dana',
        `25000'
       );
    // show first result set
    SELECT * FROM EmpTable AS FirstPass;
    // now perform updates
    UPDATE EmpTable SET EmpSalary = EmpSalary * 1.10
       WHERE EmpSalary > 30000;
    UPDATE empTable SET EmpSalary = EmpSalary * 1.15
       WHERE EmpSalary <= 30000;
    // show new results
    SELECT * FROM EmpTable AS SecondPass;
    // eof

In Listing 15.7, you use the `WHERE` clause to isolate the records you want to modify with the `UPDATE_SET` statement. The `WHERE` clause can be as simple or as complicated as needed to meet the criteria. In other words, any `WHERE` clause that is valid within the `SELECT_FROM` statement can be used as part of the `UPDATE_SET` statement.
Figure 15.8. The results of the `UPDATE` query with a `WHERE` clause.

####
Creating Make Table Queries Using the SELECT_INTO_FROM Statement

The `SELECT_INTO_FROM` statement allows you to create entirely new tables, complete with data from existing tables. This is called a Make Table query because it enables you to make a new table. The difference between Make Table queries and the `CREATE TABLE` statement is that you use the Make Table query to copy both the table structure and the data within the table from an already existing table. Because the Make Table query is really just a form of a `SELECT` statement, you can use all the clauses valid for a `SELECT` statement when copying data tables, including `WHERE`, `ORDER BY`, `GROUP BY`, and `HAVING`.

Make Table queries are excellent for making backup copies of your data tables. You can also create static read-only tables for reporting and reviewing purposes. For example, you can create a Make Table query that summarizes sales for the period and save the results in a data table that can be accessed for reports and on-screen displays. Now you can provide summary data to your users without giving them access to the underlying transaction tables. This can improve overall processing speed and help provide data security, too.

The basic form of the Make Table query is

    SELECT field1, field2 INTO DestinationTable FROM SourceTable;

In the preceding example, the field1`,` field2 list contains the list of fields in the SourceTable that is copied to the DestinationTable. If you want to copy all the columns from the source to the destination, you can use the asterisk wildcard (`*`) character for the field list. Enter the `SQL-VB5` script in Listing 15.8 as `SQLVB07.SQV`. Save and execute the script, and check your on-screen results against those in Figure 15.9.

### Listing 15.8. Testing Make Table queries.

    //
    // sqlvb07.sqv - Testing Make Table Queries
    //
    // create a database
    dbMake sqlvb07.mdb;
    // create a base table
    CREATE TABLE BaseTable
       (CustID TEXT(10) CONSTRAINT PKBaseTable PRIMARY KEY,
        CustName TEXT(30),
        CustBalance CURRENCY,
        CustType TEXT(10),
        Notes MEMO
       );
    // add some data
    INSERT INTO BaseTable VALUES
       (`CUST01',
        `Willingham & Associates',
        `300.65',
        `RETAIL',
        `This is a comment'
       );
    INSERT INTO BaseTable VALUES
       (`CUST02',
        `Parker & Parker',
        `1000.29',
        `WHOLESALE',
        `This is another comment'
       );
    INSERT INTO BaseTable VALUES
       (`CUST03',
        `Anchor, Smith, & Hocking',
        `575.25',
        `RETAIL',
        `This is the last comment'
       );
    // now make a new table from the old one
    SELECT * INTO CopyTable FROM BaseTable;
    // show results
    SELECT * FROM BaseTable;
    SELECT * FROM CopyTable;
    // eof

Figure 15.9. The results of a simple Make Table query.
In Listing 15.8, you created a database with one table, populated the table with some test data, and then executed a Make Table query that copied the table structure and contents to a new table in the same database.

You can use the `WHERE` clause to limit the rows copied to the new table. Modify `SQLVB07.SQV` to contain the new `SELECT_INTO` statement and its corresponding `SELECT_FROM`, as shown in Listing 15.9. Save the script and execute it. Your results should look similar to those shown in Figure 15.10.

### Listing 15.9. Using the WHERE clause to limit Make Table queries.

    //
    // sqlvb07.sqv - Testing Make Table Queries
    //
    // create a database
    dbMake sqlvb07.mdb;
    // create a base table
    CREATE TABLE BaseTable
       (CustID TEXT(10) CONSTRAINT PKBaseTable PRIMARY KEY,
        CustName TEXT(30),
        CustBalance CURRENCY,
        CustType TEXT(10),
        Notes MEMO
       );
    // add some data
    INSERT INTO BaseTable VALUES
       (`CUST01',
        `Willingham & Associates',
        `300.65',
        `RETAIL',
        `This is a comment'
       );
    INSERT INTO BaseTable VALUES
       (`CUST02',
        `Parker & Parker',
        `1000.29',
        `WHOLESALE',
        `This is another comment'
       );
    INSERT INTO BaseTable VALUES
       (`CUST03',
        `Anchor, Smith, & Hocking',
        `575.25',
        `RETAIL',
        `This is the last comment'
       );
    // now make a new table from the old one
    SELECT * INTO CopyTable FROM BaseTable;
    // select just some of the records
    SELECT * INTO RetailTable FROM BaseTable
       WHERE CustType='RETAIL';
    // show results
    SELECT * FROM BaseTable;
    SELECT * FROM CopyTable;
    SELECT * FROM RetailTable;
    // eof

As you can see from Figure 15.10, only the rows with `WHERE` `CustType = `RETAIL'` are copied to the new table.

You can also use the `GROUP BY` and `HAVING` clauses to limit and summarize data before copying to a new table. Let's modify the `SQLVB07.SQV` script to produce only one record for each customer type, with each new row containing the customer type and total balance for that type. Let's also order the records in descending order by customer balance. Let's rename the CustBalance field to Balance. The modifications to `SQLVB07.SQV` are shown in Listing 15.10. Make your changes, save and run the script, and compare your results to Figure 15.11.
Figure 15.10. Using the `WHERE` clause to limit Make Table queries.

###
Listing 15.10. Using GROUP BY and HAVING to summarize data.

    //
    // sqlvb07.sqv - Testing Make Table Queries
    //
    // create a database
    dbMake sqlvb07.mdb;
    // create a base table
    CREATE TABLE BaseTable
       (CustID TEXT(10) CONSTRAINT PKBaseTable PRIMARY KEY,
        CustName TEXT(30),
        CustBalance CURRENCY,
        CustType TEXT(10),
        Notes MEMO
       );
    // add some data
    INSERT INTO BaseTable VALUES
       (`CUST01',
        `Willingham & Associates',
        `300.65',
        `RETAIL',
        `This is a comment'
       );
    INSERT INTO BaseTable VALUES
       (`CUST02',
        `Parker & Parker',
        `1000.29',
        `WHOLESALE',
        `This is another comment'
       );
    INSERT INTO BaseTable VALUES
       (`CUST03',
        `Anchor, Smith, & Hocking',
        `575.25',
        `RETAIL',
        `This is the last comment'
       );
    // now make a new table from the old one
    SELECT * INTO CopyTable FROM BaseTable;
    // select just some of the records
    SELECT * INTO RetailTable FROM BaseTable
       WHERE CustType='RETAIL';
    // create a new summary table with fancy stuff added
    SELECT CustType, SUM(CustBalance) AS Balance INTO SummaryTable
       FROM BaseTable
       GROUP BY CustType;
    // show results
    SELECT * FROM BaseTable;
    SELECT * FROM CopyTable;
    SELECT * FROM RetailTable;
    SELECT * FROM SummaryTable;
    // eof

Figure 15.11. Using `GROUP BY` and `HAVING` to summarize data.
In all the examples so far, you have used the `SELECT_INTO` statement to copy existing tables to another table within the database. You can also use `SELECT_INTO` to copy an existing table to another database by adding the `IN` clause. You can use this feature to copy entire data tables from one database to another, or to copy portions of a database or data tables to another database for archiving or reporting purposes.

For example, if you want to copy the entire BaseTable you designed in the previous examples from `SQLVB07.MDB` to `SQLVB07`B`.MDB`, you could use the following `SELECT_INTO` statement:

    SELECT * INTO CopyTable IN sqlvb07b.mdb FROM BaseTable;

You can use all the `WHERE`, `ORDER BY`, `GROUP BY`, `HAVING`, and `AS` clauses you desire when copying tables from one database to another.

> WARNING: When you copy tables using the `SELECT_INTO` statement, none of the indexes or constraints are copied to the new table. This is an important point. If you use `SELECT_INTO` to create tables that you want to use for data entry, you need to reconstruct the indexes and constraints using `CREATE INDEX` to add indexes and `ALTER TABLE` to add constraints.

#### Creating Delete Table Queries Using DELETE_FROM

The final SQL statement you learn today is the `DELETE_FROM` statement, commonly called the Delete Table query. Delete Table queries are used to remove one or more records from a data table. The delete query can also be applied to a valid view created using the `JOIN` keyword. Although it is not always efficient to use the `DELETE` statement to remove a single record from a table, it can be very effective to use the `DELETE` statement to remove several records from a table. In fact, when you need to remove more than one record from a table or view, the `DELETE` statement outperforms repeated uses of the `Delete` method in Visual Basic code.

In its most basic form, the `DELETE` statement looks like this:

    DELETE FROM TableName;

In the preceding example, TableName represents the name of the base table from which you are deleting records. In this case, all records in the table are removed using a single command. If you want to remove only some of the records, you could add an SQL `WHERE` clause to limit the scope of the `DELETE` action.

    DELETE FROM TableName WHERE Field = value;

This example removes only the records that meet the criteria established in the `WHERE` clause.

Now let's create some real `DELETE` statements using `SQL-VB`. Start a new script file called `SQLVB08.SQV`, and enter the script commands in Listing 15.11. Save the script and execute it. Check your results against those shown in Figure 15.12.

### Listing 15.11. Using the DELETE statement.

    //
    // sqlvb08.sqv - Testing DELETE statements
    //
    // create a new database
    dbMake sqlvb08.mdb;
    // create a table to work with
    CREATE TABLE Table1
       (RecID TEXT(10),
        LastName TEXT(30),
        FirstName TEXT(30),
        RecType TEXT(5),
        Amount CURRENCY,
        LastPaid DATE
       );
    // add some records to work with
    INSERT INTO Table1 VALUES
       (`R01',
        `Simmons',
        `Chris',
        `LOCAL',
        `3000',
        '12/15/95'
       );
    INSERT INTO Table1 VALUES
       (`R02',
        `Walters',
        `Curtis',
        `INTL',
        `5000',
        '11/30/95'
       );
    INSERT INTO Table1 VALUES
       (`R03',
        `Austin',
        `Moro',
        `INTL',
        `4500',
        '01/15/96'
       );
    // show loaded table
    SELECT * FROM Table1 AS FirstPass;
    // now delete LOCAL records
    DELETE FROM Table1
       WHERE RecType = `LOCAL';
    // show results
    SELECT * FROM Table1 AS SecondPass;
    // eof

Figure 15.12. The results of a simple `DELETE` statement.
The `SQLVB08.SQV` script in Listing 15.11 creates a database with one table in it, populates that table with test data, and then shows the loaded table. Next, a `DELETE` statement is executed to remove all records that have a `Table1.RecType` that contains `LOCAL`. When this is done, the results are shown on-screen.

You can create any type of `WHERE` clause you need to establish the proper criteria. For example, what if you want to remove all international (INTL) records where the last payment is after 12/31/95? Edit your copy of `SQLVB08.SQV`. Then save and run it to check your results against Figure 15.13. Our version of the solution appears in Listing 15.12.

### Listing 15.12. Using a complex WHERE clause with a DELETE statement.

    //
    // sqlvb08.sqv - Testing DELETE statements
    //
    // create a new database
    dbMake sqlvb08.mdb;
    // create a table to work with
    CREATE TABLE Table1
       (RecID TEXT(10),
        LastName TEXT(30),
        FirstName TEXT(30),
        RecType TEXT(5),
        Amount CURRENCY,
        LastPaid DATE
       );
    // add some records to work with
    INSERT INTO Table1 VALUES
       (`R01',
        `Simmons',
        `Chris',
        `LOCAL',
        `3000',
        #12/15/95#
       );
    INSERT INTO Table1 VALUES
       (`R02',
        `Walters',
        `Curtis',
        `INTL',
        `5000',
        #11/30/95#
       );
    INSERT INTO Table1 VALUES
       (`R03',
        `Austin',
        `Moro',
        `INTL',
        `4500',
        #01/15/96#
       );
    // show loaded table
    SELECT * FROM Table1 AS FirstPass;
    // now delete LOCAL records
    DELETE FROM Table1
       WHERE RecType = `INTL' AND LastPaid > #12/31/95#;
    // show results
    SELECT * FROM Table1 AS SecondPass;
    // eof

As you can see from the code in Listing 15.12, you only need to change the `WHERE` clause (adding the date criteria) in order to make the `DELETE` statement function as planned.

> NOTE: You might have noticed that you enclose date information with the pound symbol (#). This ensures that Microsoft Jet handles the data as DATE type values. Using the pound symbol works across language settings within the Windows operating system. This means that if you ship your program to Europe, where many countries use the date format DD/MM/YY (instead of the U.S. standard MM/DD/YY), Windows converts the date information to display and compute properly for the regional settings on the local PC.

Figure 15.13. The results of the `DELETE` statement with a complex `WHERE` clause.
You can also use the `DELETE` statement to delete records in more than one table at a time. These multitable deletes can only be performed on tables that have a one-to-one relationship. The example in Listing 15.13 shows modifications to `SQLVB08.SQV` to illustrate the use of the `JOIN` clauses to create a multitable `DELETE` statement. Use `SQL-VB5` to edit your copy of `SQLVB08.SQV` to match the one in Listing 15.13. Save and execute the script and refer to Figure 15.14 for comparison.

### Listing 15.13. Using JOIN to perform a multitable DELETE.

    //
    // sqlvb08.sqv - Testing DELETE statements
    //
    // create a new database
    dbMake sqlvb08.mdb;
    // create a table to work with
    CREATE TABLE Table1
       (RecID TEXT(10),
        LastName TEXT(30),
        FirstName TEXT(30),
        RecType TEXT(5),
        Amount CURRENCY,
        LastPaid DATE
       );
    // add some records to work with
    INSERT INTO Table1 VALUES
       (`R01',
        `Simmons',
        `Chris',
        `LOCAL',
        `3000',
        #12/15/95#
       );
    INSERT INTO Table1 VALUES
       (`R02',
        `Walters',
        `Curtis',
        `INTL',
        `5000',
        #11/30/95#
       );
    INSERT INTO Table1 VALUES
       (`R03',
        `Austin',
        `Moro',
        `INTL',
        `4500',
        #01/15/96#
       );
    // create a second table for JOIN purposes
    CREATE TABLE Table2
       (RecID TEXT(10),
        BizPhone TEXT(20),
        EMailAddr TEXT(30)
       );
    // load some data
    INSERT INTO Table2 VALUES
       (`R01',
        `(111)222-3333',
        `chris@link.net'
       );
    INSERT INTO Table2 VALUES
       (`R03',
        `(777)888-9999',
        `moro@band.edu'
       );
    INSERT INTO Table2 VALUES
       (`R04',
        `(222)444-6666',
        `person@mystery.uk'
       );
    // show loaded table
    SELECT * FROM Table1 AS FirstPass1;
    SELECT * FROM Table2 AS FirstPass2;
    // now delete records
    DELETE Table1.*, Table2.* FROM
       Table1 INNER JOIN Table2 ON Table1.RecID = Table2.RecID;
    // show results
    SELECT * FROM Table1 AS SecondPass1;
    SELECT * FROM Table2 AS SecondPass2;
    // eof

Figure 15.14. Results of a multitable `DELETE`.
The results of this `DELETE` query might surprise you. Because there is no `WHERE` clause in the `DELETE` statement that could limit the scope of the SQL command, you might think that the statement deletes all records in both tables. In fact, this statement only deletes the records that have a matching RecID in both tables. The reason for this is that you used an `INNER JOIN`. `INNER JOIN` clauses operate only on records that appear in both tables. You now have an excellent way to remove records from multiple tables with one `DELETE` statement! It must be pointed out, however, that this technique only works with tables that have a one-to-one relationship defined. In the case of one-to-many relationships, only the first occurrence of the match on the many side is removed.

Here is a puzzle for you. What happens if you only list `Table1` in the first part of that last `DELETE` statement?

    DELETE Table1.* FROM
    Table1 INNER JOIN Table2 ON Table1.RecID = Table2.RecID;

What records (if any) are deleted from Table1? Edit `SQLVB08.SQV`, save it, and execute it to find out. Check your results against Figure 15.15.

As you can see from Figure 15.15, a `DELETE` query that contains an `INNER JOIN` only removes records from Table1 that have a match in Table2. And the records in Table2 are left intact! This is a good example of using `JOIN` clauses to limit the scope of a `DELETE` statement. This technique is very useful when you want to eliminate duplicates in related or identical tables. Note also that this `INNER JOIN` works just fine without the use of defined constraints or index keys.
Figure 15.15. The results of a one-sided `DELETE` using an `INNER JOIN`.

###
Summary

You have learned how to add, delete, and edit data within tables using the DML (Data Manipulation Language) SQL keywords. You've learned that, by using DML statements, you can quickly create test data for tables and load default values into startup tables. You also learned that DML statements--such as Append queries, Make Table queries, and Delete queries--can outperform equivalent Visual Basic code versions of the same operations.

You learned how to manage data within the tables using the following DML keywords:

- The `INSERT INTO` statement can be used to add new rows to the table using the `VALUES` clause.

- You can create an Append query by using the `INSERT INTO_FROM` syntax to copy data from one table to another. You can also copy data from one database to another using the `IN` clause of an `INSERT INTO_FROM` statement.

- You can create new tables by copying the structure and some of the data using the `SELECT_INTO` statement. This statement can incorporate `WHERE`, `ORDER BY`, `GROUP BY`, and `HAVING` clauses to limit the scope of the data used to populate the new table you create.

- You can use the `DELETE FROM` clause to remove one or more records from an existing table. You can even create customized views of the database using the `JOIN` clause and remove only records that are the result of a `JOIN` statement.

### Quiz

  1. What SQL statement do you use to insert a single data record into a table? What is the basic form of this statement?
  2. What SQL statement do you issue to insert multiple data records into a table? What is its format?
  3. What SQL statement do you use to modify data that is already in a data table? What is the form of this statement?
  4. What SQL statement is used to create new tables that include data from other tables? What is the format of this statement?
  5. What SQL statement do you use to delete one or more records from a data table? What is the basic format of this statement?

### Exercises

  1. Modify the `SQL-VB5` script you created in Exercise 1 of Day 13 to add the following records.

> Data for the CustomerType Table

> Customer Type | Description
> `INDV` | Individual
> `BUS` | Business--Non-Corporate
> `CORP` | Corporate Entity

Data for the Customers Table

Field | Customer #1 | Customer #2 | Customer #3
CustomerID | SMITHJ | JONEST | JACKSONT
Name | John Smith | Jones Taxi | Thomas Jackson
CustomerType | INDV | BUS | INDV
Address | 160 Main Street | 421 Shoe St. | 123 Walnut St.
City | Dublin | Milford | Oxford
State | Ohio | Rhode Island | Maine
Zip | 45621 | 03215 | 05896
Phone | 614-555-8975 | 555-555-5555 | 444-444-4444
Fax | 614-555-5580 | 555-555-5555 | 444-444-4444

  2. Create a third table that includes data from the CustomerID, City, and State fields of the Customers table. Call your table Localities.
  3. Write an SQL statement that would delete the SMITHJ record from the Customers table. What SQL statement would you issue to delete the entire Customers table?

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.