Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

Using SQL to Update Data

SQL can be used not only to retrieve data, but to update it as well. This concept was introduced in the section "Action Queries," which focused on the SQL statements behind the Action queries.

The UPDATE Statement

The UPDATE statement is used to modify the data in one or more columns of a table. The syntax for the UPDATE statement is

UPDATE table/query
SET column1=expression1 [,column2=expression2] [,...]
[WHERE

criteria ]

The WHERE clause in the UPDATE statement is used to limit the rows that are updated. The following is an example of an UPDATE statement:

UPDATE tblClients
SET tblClients.DefaultRate = [DefaultRate]*1.1
WHERE tblClients.DefaultRate<=125

This statement updates the DefaultRate column of the tblClients table, increasing it by 10% for any clients that have a default rate less than or equal to 125.

The DELETE Statement

Whereas the UPDATE statement is used to update all rows that meet specific criteria, the DELETE statement deletes all rows that meet the specified criteria. The syntax for the DELETE statement is

DELETE FROM

table [WHERE

criteria ]

As with the UPDATE statement, the WHERE clause is used to limit the rows that are deleted. The following is an example of the use of a DELETE statement:

DELETE tblClients.*, tblClients.DefaultRate
FROM tblClients
WHERE tblClients.DefaultRate<=125

This statement deletes all clients from the tblClients table whose DefaultRate field is less than or equal to 125.

The INSERT INTO Statement

The INSERT INTO statement is used to copy rows from one table to another. The syntax for the INSERT INTO statement is

INSERT INTO target-table select-statement [WHERE criteria]

Once again, the optional WHERE clause is used to limit the rows that are copied. Here's an example:

INSERT INTO tblCheapClients
(ClientID, CompanyName, ContactFirstName,
ContactLastName, ContactTitle, DefaultRate )
SELECT tblClients.ClientID, tblClients.CompanyName, tblClients.ContactFirstName,
tblClients.ContactLastName, tblClients.ContactTitle, tblClients.DefaultRate
FROM tblClients
WHERE tblClients.DefaultRate<=125

This statement inserts the ClientID, CompanyName, ContactFirstName, ContactLastName, ContactTitle, and DefaultRate fields into the corresponding fields in the tblCheapClients table for any clients whose DefaultRate field is less than or equal to 125.

The SELECT INTO Statement

Whereas the INSERT INTO statement inserts data into an existing table, the SELECT INTO statement inserts data into a new table. The syntax looks like this:

SELECT

column1 [,column2 [,...]] INTO

new-table
FROM

table-list
[WHERE

where-clause ]
[ORDER BY orderby-clause]

The WHERE clause is used to determine which rows in the source table are inserted into the destination table. The ORDER BY clause is used to designate the order of the rows in the destination table. Here's an example:

SELECT tblClients.ClientID, tblClients.CompanyName,
tblClients.ContactFirstName, tblClients.ContactLastName,
tblClients.ContactTitle, tblClients.DefaultRate
INTO tblCheapClients
FROM tblClients
WHERE tblClients.DefaultRate)<=125

This statement inserts data from the selected fields in the tblClients table into a new table called tblCheapClients. Only the clients whose DefaultRate field is less than or equal to 125 are inserted.