ADO: ActiveX Data Objects

Data Manipulation

SQL is much more than just a data-retrieving language. It is also manipulates data within a given data source. The three types of data manipulation are the addition of data, the modification of data, and the removal of data.

Adding Records

Adding records in SQL is very easy. It is done through an append query. This type of query can add one or more records to an existing table through the INSERT...INTO clause.

INSERT . . . INTO

The INSERT...INTO clause syntax for appending a single record is as follows:

INSERT INTO target_table
[IN external_database]
            [(field_name1 [, field_name2 [, ...]])]
SELECT [source_table].field_name1 [, field_name2 [, ...]
FROM source_table

The following example inserts multiple records into the Automobile table, based on cost, from an additional table, NewAutomobiles:

INSERT INTO Automobile
SELECT *
FROM NewAutomobiles
WHERE (Cost >= 100000);

The INSERT...INTO clause syntax for appending multiple records is as follows:

INSERT INTO target_table
            [(field_name1 [, field_name2 [, ...]])]
VALUES (value1 [, value2 [, ...]])

The following SQL statement adds a new record to the Automobile table:

INSERT INTO Automobile
            (EmployeeID, 
             Year,
             Make,
             Model,
             Color,
             Cost)
VALUES      (100,
             1997,
             Chevy
             Cavalier,
             Teal,
             14000);

Modifying Records

To modify values of a field in an existing table, use an update query.

UPDATE

The UPDATE clause syntax is:

UPDATE table_name
SET new_value_of_field
WHERE criteria_for_update

The following example increments the value of the TotalRevenue ...