904 Chapter 20 DATABASES: ARCHITECTURE AND BASIC CONCEPTS

Action Queries

In addition to the selection queries we examined so far, you can also execute queries that alter the data in the database’s tables. These queries are called action queries, and they’re quite simple compared to the selection queries. There are three types of actions you can perform against a database: insertions of new rows, deletions of existing rows, and updates (edits) of existing rows. For each type of action there’s a SQL statement, appropriately named INSERT, DELETE, and UPDATE. Their syntax is very simple, and the only complication is how you specify the affected rows (for deletions and updates). As you can guess, the rows to be affected are specified with a WHERE clause, followed by the criteria we have discussed in selection queries.

The first difference between action and selection queries is that action queries don’t return any rows. They return the number of rows affected, but you disable this feature by calling the statement:

SET NOCOUNT ON

This statement can be used when working with a SQL Server database. Let’s look at the syntax of the three action SQL statements, starting with the simplest, the DELETE statement.

Deleting Rows

The DELETE statement deletes one or more rows from a table, and its syntax is:

DELETE table_name WHERE criteria

The WHERE clause specifies the criteria that the rows must meet in order to be deleted. The criteria expression is no different than the criteria you specify in the WHERE clause of selection query. To delete the orders placed before 1998, use a statement like

USE NORTHWIND

DELETE Orders

WHERE OrderDate < ‘1/1/1998’

Of course, the specified rows will be deleted only if the Orders table allows cascade deletions, or if the rows to be deleted are not linked to related rows.

Inserting New Rows

The syntax of the INSERT statement is:

INSERT table_name (column_names) VALUES (values)

column_names and values are comma-separated lists of columns and their respective values. Values are mapped to their columns by the order in which they appear in the two lists.

Notice that you don’t have to specify values for all columns in the table, but the values list must contain as many items as there are column names in the first list. To add a new row to the Customers table use a statement like the following:

INSERT Customers (CustomerID, CompanyName) VALUES (‘FRYOG’, ‘Fruit & Yogurt’)

This statement will insert a new row, provided that the FRYOG key isn’t already in use. Only two of the new row’s columns are set, and they’re the columns that can’t accept Null values.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

STRUCTURED QUERY LANGUAGE 905

If you want to specify values for all the columns of the new row, you can omit the list of columns. The following statement retrieves a number of rows from the Products table and inserts them into the SelectedProducts table, which has the exact same structure:

INSERT INTO SelectedProducts VALUES (values)

If the values come from a table, you can replace the VALUES keyword with a SELECT statement:

INSERT INTO SelectedProducts

SELECT * FROM Products WHERE CategoryID = 4

There are more variations of the INSERT statement, but in this book we’ll use the simplest form, where you specify both the column names and their values. The wizard we’ll explore the in the following chapter generates statements like the following to insert a new row:

INSERT INTO dbo.Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)

VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)

The variables @CustomerID, @CompanyName, and so on are the values of the fields of the newly created row—these values were specified by the user through the appropriate interface.

Editing Existing Rows

The UPDATE statement edits a row’s fields, and its syntax is

UPDATE table_name SET field1 = value1, field2 = value2, …

WHERE criteria

The criteria expression is no different than the criteria you specify in the WHERE clause of selection query. To change the country from “UK” to “United Kingdom” in the Customers table, use the following statement:

UPDATE Customers SET Country=’United Kingdom’

WHERE Country = ‘UK’

This statement will locate all the rows in the Customers table that meet the specified criteria (their Country field is “UK”) and change this field’s value to “United Kingdom.”

In Chapter 21, you’ll see UPDATE statements like the one in Listing 20.1, which are executed to update the underlying table in the database. These statements are created automatically by the appropriate wizard and become part of the application. The SQL statement in Listing 20.1 was generated automatically to update the Customers table. The variables @CustomerID, @CompanyName, and so on are the new values of the fields. The variables @Original_CustomerID and @Original_CompanyName are the values read from the database. The SET clause of the statement is quite simple: it sets the values of the fields to the new values. The WHERE statement is a little complicated, because this UPDATE statement won’t change the table if the values of the fields are not the same as the ones read from the database.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com