Using SQL Action Statements

As you have seen, the SELECT statement is used for returning records from a database. However, there are several other types of SQL statements, known as action queries, that do not return records. These queries, listed next, just manipulate data in the database:

• DELETE FROM— An action query that removes records from a table

• INSERT INTO— An action query that adds records to a table

• UPDATE— An action query that sets the values of fields in a table

As you will see in the following sections, action queries work very similarly to SELECT statements in that you can join tables and specify a WHERE clause.

Using the DELETE Statement

The DELETE statement’s purpose is to delete specific records from a table. You specify the records using a WHERE clause. The format of the DELETE statement is as follows:

DELETE FROM tablename [WHERE where clause] 

The WHERE clause is an optional parameter. If you omit the WHERE clause, then all records in the table are deleted. The following is an example of a DELETE statement:

DELETE FROM Votes WHERE State='FL' 

As you can see, the syntax of a simple DELETE statement is straightforward. However, you can also join to other tables to help filter the records to delete:

DELETE Employee FROM Employee E JOIN Person P ON E.SSN = P.SSN WHERE 
P.LastName='Thrasher' and P.FirstName='Marge' 

The previous SQL statement (which works on SQL Server but not Access) only deletes a record from the Employee table. Although the Person table is used to look up the record to delete by name, its contents are unchanged by the statement.

Using the INSERT Statement

Like the DELETE statement, the INSERT statement is another action query. There are two basic forms of the INSERT statement:

INSERT INTO tablename SELECT rest-of-select statement 
INSERT INTO tablename (fieldlist) VALUES (valuelist) 

The first form is generally used to copy records from one table to another, but can also be used to insert literal values. The second form is used to insert literal values provided by the user or parameters of a stored procedure.

Copying Records from Another Table

In the form of INSERT that uses a SELECT, you build the SELECT portion of the statement exactly as you would any other SELECT statement. Let’s suppose a rival corporation, who happens to use the same database, buys our country club. In order to merge the employee tables, we could copy the records from one table to another using INSERT:

INSERT INTO Employee SELECT SSN, Department, Salary FROM OldEmployees 

When an INSERT INTO is used in this manner, you have to make sure to include a value for each field in the new table, as well as make sure the order of the field list is correct. (This example also assumes there are no duplicates in the two tables. In reality, you might need to make the query more robust by adding a WHERE NOT EXISTS clause to avoid SSN key violations.)

Inserting New Records

You do not have touse records from another table with INSERT INTO statement. Consider the following examples, which add new records to the Employee table:

INSERT INTO Employee SELECT '000-11-2222', 'Housekeeping',12000 
INSERT INTO Employee (SSN,Department,Salary) VALUES ('111-22-3333','Valet',10000) 

As with the previous example, when using the form of INSERT with SELECT, you have to be sure the order and number of fields matches the destination table. The second example, which uses an explicit field list with a VALUES clause, is a safer method when you are adding new records. Suppose, for example, a new field was added to the table or the field order was changed. As long as the data types and field count matched, the INSERT INTO… SELECT will not fail and might actually put the wrong data in a field. The second form of the INSERT (with the VALUES clause) provides a more definite order. The first item in the values list is put in the first field in the field list, and so on. The designer of the table can actually add new fields and, as long as they are not required, the INSERT query will continue to work without changes.

Using the UPDATE Statement

The UPDATE statement is used to change the values of specific fields in a table. The syntax of the UPDATE statement is as follows:

UPDATE tablename SET fieldname=newvalue [,fieldname=newvalue][WHERE where clause] 

If one of the ladies in your Person table gets married, you need to update her LastName field. The following UPDATE statement would change her record:

UPDATE Person SET LastName = 'Smith' WHERE SSN = '111-22-3333' 

If you’re wondering why we used the SSN rather than the old last name in the WHERE clause, the answer is because we only want to update a specific employee’s record, and there is no guarantee other employees do not have the same last name.

In addition to using literal values, UPDATE also allows you to use field values and calculations. For example, suppose a hacker gains access to your Employee table and decides to double everyone’s salary:

UPDATE Employee SET Salary = Salary * 2 

The sample statement takes the existing salary, multiplies it by two, and assigns the result back to the Salary field. Because there is no WHERE clause, all records in the Employee table are affected.

Transactions and Data Integrity

A transaction is a unit of work that involves multiple sub-parts, but must succeed or fail as a whole. A real-life example is a bank account transfer of money. There are two parts: subtracting money from one account and adding it to another account. However, the transaction must succeed or fail as a whole, because only performing half of it would leave an account out of balance.

In the database world, transactions provide data integrity if a SQL statement fails to execute properly. As an extreme example, suppose you have to execute 20 INSERT statements just to add an order to your database. If the twelfth INSERT statement fails, (for key violation or other reason) what do you do? Leaving the database in an inconsistent state is a bad idea, because future joins might not work properly. However, by defining all of your inserts as a single transaction, you can have the database automatically roll back any changes in the event of an error.

Although transactions are important and we are about to tell you some SQL statements to control them, we don’t recommend the SQL commands! An even easier way to manage transactions is from within Visual Basic objects. You can actually create transactions across one or more Visual Basic classes. Methods within these classes can execute SQL queries and determine whether to roll back or commit the transaction from within your Visual Basic code.

In the context of this chapter, transactions come in handy when testing SQL statements. For example, if you accidentally update or delete the wrong records, you can roll back your transaction. The following statements are used to define a transaction in SQL:

• BEGIN TRANSACTION— Start a new transaction.

• COMMIT— Tell the database the transaction is complete.

• ROLLBACK— Cancel a transaction in progress.

As an example of undoing an accidental delete, execute the following statements:

BEGIN TRANS 
DELETE FROM Person Where FirstName='Marge' 

Now, verify that Marge’s record is gone with the following query:

SELECT * FROM Person Where FirstName='Marge' 

Finally, execute the ROLLBACK command to undo your delete. Execute the SELECT command and notice Marge has reappeared! If you had executed the COMMIT statement instead of the ROLLBACK, the record would be permanently deleted.